Hi All,
I have a table of costs in all different currencies and want to SUM them at the bottom, in one currency, without having to create a separate table to convert each line.
Is there a simple way to do this without VBA? I have been trying SUMPRODUCT of an Index match formula, but can't quite get it to work. It's summing them all at the conversion rate of the first line rather than calculating each line individually and then summing at the end.
{=SUMPRODUCT(INDEX(Control!$D$3:$D$149,MATCH(A2:A5,Control!$C$3:$C$149,0)),B2:B5)}
Simplified Table as an example (FX rates in a separate table in Control tab):
A B
Currency Value
GBP 100
CHF 200
EUR 300
CHF 400
TOTAL (USD) X?
Any advice would be greatly appreciated!!
Bookmarks