Sumif formula within sumproduct

[Keith Morrice]

Can any one explain how the attached formula works please. I inherited the following spreadsheet and it doesn't make sense to me at all.

A B C D E
1 Currency Amount Currency Rate
2 GBP 1618 USD 1.00
3 GBP 488 GBP 0.72
4 GBP 4156 NOK 6.90
5 GBP 2838 AUD 1.51
6 GBP 1571 SGD 1.50
7 GBP 577 BRR 2.34
8 EUR 6709 EUR 0.77
9 GBP 19 DKK 5.71
10 EUR 49 NGN 149.25
11 GBP 2615 MXN 13.80
12 USD 2187 ZAR 10.00
13 NOK 510 VEB 2.15
14 AON 75.02
15 USD 30484
16
17 =SUMPRODUCT($B$2:$B$13,1/SUMIF($D$2:$E$14,$A$2:$A$13,$E$2:$E$14))



The formula in B15 is converting all the amounts in the denoted currencies to USD. It works like a dream but I don't understand how.
Thanks in anticipation

 

[msaiyad]

There should be some hidden rows
find out

 

[gecs]

The formula does exactly what its name says: sums the products of the amounts in column B and the exchange rate found in the C2:D14 range. Actually the real formula according to the shown data should be:
=SUMPRODUCT($B$2:$B$13,1/SUMIF($C$2:$D$14,$A$2:$A$13,$D$2:$D$14))
SUMPRODUCT works with arrays, so the first parameter is the array of amounts and the second parameter is the array of exchange rates corresponding to each currency involved into calculation. For the second parameter the SUMIF function is used to create the array of exchange rates. It works like a VLOOKUP, but the result SUMIF returns can be converted to an array, while using VLOOKUP will result in an error.

 

[Keith Morrice]

Thanks very much for that - excellent.