# Sum multiple currencies by looking up conversion rates based on multiple cell values

I'm trying to sum values that are in different currencies. The currency for each value is specified in the previous column and the final result should be in Australian dollars AUD.

I use a fixed currency table for the conversion rates.

Conversion_Table

A B C D E
1 GBP USD SGD AUD
2 GBP 1      .7   .5 1.1
3 USD 1.1 1    1.4 1.7
4 SGD   .8 1.1 1     .5
5 AUD   .6   .9 1.3

Expected Result

A B C
1 Item Curr Amt
2 Widget1 GBP 1
3 Widget2 USD 1
4 Widget3 SGD 1
5 TOTAL `AUD` `3.3`

The TOTAL in `C5` is `3.3` Because the values in `C2:C4` once converted to `AUD` are `{1.1; 1.7; 0.5}` after cross-referencing the `Conversion_Table` for the rates.

I have tried various combinations of SUMPRODUCT, ARRAYFORMULA, SUM, and SUMIF to no avail.

I'm trying to avoid helper cells, I have to allow for expansion into additional currencies, and I can't use GOOGLEFINANCE to look up conversion rates because they are user-defined (fixed) at various points in time. And, of course, the destination currency can be selected by the user so changes (generally based on the location of the user).

I'm currently doing an INDEX/MATCH to locate the correct conversion rate in the table. Eg, for Widget1, I look up GBP (source currency) to AUD (destination currency) and multiply that rate (1.1) by the value (1). But the SUMPRODUCT I'm using is getting "stuck" on the first source currency listed - GBP - and using the GBP->AUD conversion rate for every Widget. I can't get the MATCH to "follow along" to each successive line, the "B2:B4" bit in the formula below.

``````=sumproduct(
C\$2:C\$4*INDEX(\$A\$12:\$E\$16,
match(B2:B4,\$A\$12:\$A\$16,0),
match(B5,\$A\$12:\$E\$12,0)))
``````

Hoping for a simple, straightforward formula since I have hundreds of lines and many, many more currencies.

## TOTAL:  SUMPRODUCT in `C6`

``````SUMPRODUCT(B2:B5,IFNA(VLOOKUP(A2:A5, E1:I, MATCH("AUD", E1:1, 0), 0)))
``````

## SUBTOTALS:  ARRAYFORMULA in `C2`

``````=ARRAYFORMULA(B2:B5*IFNA(VLOOKUP(A2:A5, E1:I, MATCH("AUD", E1:1, 0), 0)))
``````

## SUBTOTALS & TOTAL: Formula in `C2`

``````=LET(subtotals,
ARRAYFORMULA(B2:B5*IFNA(VLOOKUP(A2:A5, E1:I, MATCH("AUD", E1:1, 0), 0)),
{subtotals; SUM(subtotals)}))
``````

• Uh, I'm not having trouble LOOKING UP the rate. It's the SUM that I don't have working. Plus, you have to understand that the example is a pared down version of the real thing. That is to say, expanding the rate table to be exponentially larger than it needs to be is not really an option.
– Phil
Commented Jun 4 at 20:59
• I realized finally this is all about one currency `AUD` The answer is to help you as well as future readers., I have adapted it. Let me know if any further issues. Commented Jun 4 at 21:18
• Thanks Blindspots. Yep, your SUMPRODUCT is the one I was looking for. But it's not all about one currency. As noted in my ORIGINAL question (which has been modified by others), the destination currency can be changed by the user to display the total in whatever currency with which they are comfortable. But that's an easy modification to your SUMPRODUCT. I also had some challenges with ARRAYFORMULA because, I think (?), you rearranged the layout of my sheet. The currency rate table always appears directly BELOW the table of Widgets. I don't have the option to rearrange things. Thanks again!
– Phil
Commented Jun 4 at 22:36
• One enhancement if you have blank lines in your table: replace "A2:A5" in the VLOOKUP with "if(isblank(A2:A5),if(isblank(B2:B5),"AUD",A2:A5),A2:A5)". Effectively, this lets the Amt column be blank with no bad result but if there IS an Amt then it MUST be associated with a currency. If not then you'll get an error which seems appropriate if the user has forgotten to specify the currency. Also note that you can substitute a cell address for "AUD" as I have. That allows the destination currency to be easily user selectable/changeable.
– Phil
Commented Jun 4 at 23:20
• I wrote it to the requirements. If you have others they should be in the question not comments. ;-) If you plan to have blank lines, wrap the VLOOKUP in IFNA rather than adding `IF(ISBLANK(A2:A5),IF(ISBLANK(B2:B5),"AUD",A2:A5),A2:A5)` If there is no currency listed the row returns `0` Commented Jun 5 at 1:41