Sumproduct

[arazoe]

Hi,

How can I multiply two text variables (region * metric) to return corresponding index #. In the attached file, in cell D21 I created a list of all regions and in cell E21 I have listed all metric unique names. Now when a user selects a region and metric I'd like the result to display in cell C21. For example,

Index Region Metric
1 Global KPI 1

Region range: D27:D110
Metric range: E27:D110
Index range (#s from 1-84): C27:C110

I tried the following sumproduct formula in cell C21, but it doesn't seem to work:
=SUMPRODUCT((D27:D110=D21)*(E27:D110=E21)*(C27:C110))

Thanks!

[daddylonglegs]

There's a typo in your formula, you have a "D" which should be an "E".....and you have some trailing spaces in the data in D27:D110=D21

You could get rid of those or try using TRIM function, i.e.

=SUMPRODUCT((TRIM(D27:D110)=D21)*(E27:E110=E21),C27:C110)

[arazoe]

Yay!!! It worked, thanks!