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
Bookmarks