Excel Formula for Combining SumIF with an Exchange Rate

Question

I am trying to sum sales for each country (easy) but also convert the various currencies I operate in at the same time. I am thinking this is best accomplished by a nested if function but I can't figure out how to write it.

I want the formula to add up each countries sales and edit the currency to GBP based on the referenced exchange rate

Any help much appreciated!

Sum and convert

score 1 · Accepted Answer · answered Jul 06 '18 at 17:03

1

Use this formula:

=SUMPRODUCT(($B$3:$B$15=B18)*($D$3:$D$15)*(SUMIFS(G:G,F:F,$C$3:$C$15)))

answered Jul 06 '18 at 17:03

Scott Craner

148,073
10
49
81

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

if you try to sum sales for each country and convert the currencies in the same time (base on GBP) use this

=SUMIF(B3:B15,B18,D3:D15) * VLOOKUP(VLOOKUP(B18,B3:D15,2,FALSE),F3:G5,2,FALSE)

This the sample of data

and drag down the formular

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 06 '18 at 15:58

ivrylobs

173
6

This only works if the country and currency always match, which for Austria it does not. – Scott Craner Jul 06 '18 at 16:55
Oh i see that 's the problem – ivrylobs Jul 06 '18 at 17:07
May be need to convert before sum it's more easier – ivrylobs Jul 06 '18 at 17:10
In the data provided by the OP Both Belgium and Austria use `GBP` and `EUR` and as such your VLOOKUP will only return the first and as such it will apply the wrong multiplyer to some of the lines. Canada uses both `USD` and `GBP`. In your data set change D7 to `a` it will not change the totals. – Scott Craner Jul 06 '18 at 17:10
Yes, MarioS answered with the use of a helper column which will work, but I am not a fan of helper columns if one formula can easily do the work. – Scott Craner Jul 06 '18 at 17:11

score 0 · Answer 3 · answered Jul 06 '18 at 16:04

0

I would use this formula to convert the sales in Column E:

=IF(C3 = "EUR", D3 * $G$4, IF(C3 = "USD", D3 * $G$4,))   ..drag this down

Then I would use a SUMIF formula

=SUMIF($B$3:$B$13, B16, $E$3:$E$13)   ..drag this down

answered Jul 06 '18 at 16:04

MarioS

262
1
3
12

score 0 · Answer 4 · answered Jul 06 '18 at 16:18

Convert both ranges to Excel Tables, do a VLOOKUP or INDEX/MATCH to bring the exchange rate through to the table on the left, add another column to do the currency exchange, then make a PivotTable out of that Table, and bring in the fields you want to summarize by and report on. Look at this answer on how to make Tables and PivotTables: VBA to copy data if multiple criteria are met

Excel Formula for Combining SumIF with an Exchange Rate

4 Answers4