0

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.

1 Answer 1

0

TOTAL:  SUMPRODUCT in C6

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

Formula in C2

SUBTOTALS:  ARRAYFORMULA in C2

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

ARRAYFORMULA subtotalsin C2

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)}))

Totals & Subtotalsin C2

5
  • 1
    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.
    – Blindspots
    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
    – Blindspots
    Commented Jun 5 at 1:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.