Multi-Currency with Sumproduct and INDEX/MATCH

[Davidator]

This is driving me crazy since more than a week: I have a long table with item prices belonging to different projects (A, B, C...) Each price may be in a different currency.

I'm trying to make a formula that would sum all the values into a single price in USD, as shown in cell G1:

The formula in G1 is: G1=B2*L3+B4*L7+B5*L4. This means that the total value of "Program A" products is $ 128.0179 (USD)

Since it is a very large list and I do not know the currency that will be written in column D, I am trying to do it automatically.
I have tried with sumproduct, but I've only gotten as far as to sum the prices for a determinate program, but without adding the Fx-rate multiplicator. Cell G3 has my failed attempt:
G3=SUMPRODUCT(--(A2:A6=E3);B2:B6;INDEX(L3:L8;MATCH(C2:C6;K3:K8;0)))

Adding an additional column that directly calculates the USD value and then summing it is -unfortunately- not an option for me.

Can someone help me?

(I'd upload the example file, but I haven't seen the option for it)

Thanks,
Dave

 

[MattVizor]

Not entirely certain about this, but would this work if you made it an array formula? i.e ctrl-Shift_Enter to surround the formula with {}

 

[Davidator]

Not entirely certain about this, but would this work if you made it an array formula? i.e ctrl-Shift_Enter to surround the formula with {}

Hi Matt, unfortunately not.. I've tried that too

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--($A$2:$A$6=E3),$B$2:$B$6,SUMIFS($L$3:$L$8,$K$3:$K$8,$C$2:$C$6))<strike></strike>

 

[Davidator]

Try...

=SUMPRODUCT(--($A$2:$A$6=E3),$B$2:$B$6,SUMIFS($L$3:$L$8,$K$3:$K$8,$C$2:$C$6))<strike></strike>

omg you are a GENIUS!!!! works like a charm! thanks a lot!!

 

[MattVizor]

That is brilliant!

 

[Aladin Akyurek]

Glad to help. Thanks for the kind words.

 

[IronM]

I'm grad everything worked out for you but would be great if this was helpful for others too... Unfortunately I cannot tell what info you have in all the cells mentioned in your formula so I can adapt it to my problem