Using sumproduct with linked array

[gutkinma]

Dear Excel users,

I would very much appreciate you help in the following problem. I know there other are ways around it, however actual problem is slightly more complicated, so if you can see solutions to this exact problem, without 'ways around', I'd very much appreciate you sharing your ideas.

Given:

Some numbers & Currencies and a separate currency table:

Value	Currency
10	EUR
20	GBP
30	CHF
40	EUR
50	GBP

Currency	X-rate
EUR	1
GBP	1.2
CHF	1.3

Now I am trying to use sumproduct (as the actual problem is slightly more complicated, and I am summing arrays in sumproduct on various criteria with --) to get the final number. Also, exchange some bits of data are dynamic, other are static, so sumproduct almost gets me where I need to be.

Idea is to get sumproduct to multiply number in the 1st column of 1st table by the respective exchange rate, based on the currency of 2nd col of 1st table and 2nd table x-rate.

I am struggling to come up with the correct syntax and would very much appreciate your input.

My thoughts: =SUMPRODUCT(A2:A6,INDEX(D4:D4,match(B2:B6,C2:C4,0))).

I know exactly why this is not working - trying to match multiple values I suppose can only be done in an array formula - that's one, and I am trying to sumproduct arrays of different size - that's two.

Would very much appreciate your input. If that can be done without array formulas, would be perfect.

Many Thanks,
Michael

[gutkinma]

So in case someone else is facing similar issue, here are two options, both are not great and impose limitations:

array: {=SUMPRODUCT(A1:A6,SUMIF(C1:C3,B1:B6,D1:D3))} - only works provided your currency list has only one value against each currency.

non-array: =SUMPRODUCT(A1:A6,(--(B1:B6=C1))*D1)+SUMPRODUCT(A1:A6,(--(B1:B6=C2))*D2)+SUMPRODUCT(A1:A6,(--(B1:B6=C3))*D3) - obviously, problem is that the more currencies you have, the longer you formula becomes.

If anyone has any shorted, smoother solutions, please share them

Cheers,
Michael

[gutkinma]

As it turns out, first formula works without array, and does the job for me. Again, remember the limitation of summing if more than one 'match' occurs - you only want one exchange rate, not sum of multiple.

SOLUTION: =SUMPRODUCT(A1:A6,SUMIF(C1:C3,B1:B6,D1:D3)) without array.