How to calculate total value of a column containing mixed currencies

Question

Given the following columns, where A is the currency and B is a monetary value in that currency:

A	B
USD	50
USD	150
EUR	200
EUR	-150
AUD	300

How can I use the GOOGLEFINANCE function to return the sum the of entire value of column B in another given currency (for example, GBP)?

Any advice appreciated.

Thanks

Maybe =B1*GOOGLEFINANCE("CURRENCY:"&A1&"GBP") and drag down. I don't think it would be possible in a neat looking arrayformula to get a direct SUM() unless it will probably look terrible. Maybe some script of some sort. — JvdV

player0 player0 · Accepted Answer · 2021-02-17T13:04:26

GOOGLEFINANCE is not supported under ARRAYFORMULA so you either introduce helper column with dragable formula:

=B2*GOOGLEFINANCE("CURRENCY:"&A2&"GBP")

and then sum it:

=SUM(D2:D)

or you can construct array:

=SUM({
 B2*GOOGLEFINANCE("CURRENCY:"&A2&"GBP");
 B3*GOOGLEFINANCE("CURRENCY:"&A3&"GBP");
 B4*GOOGLEFINANCE("CURRENCY:"&A4&"GBP");
 B5*GOOGLEFINANCE("CURRENCY:"&A5&"GBP");
 B6*GOOGLEFINANCE("CURRENCY:"&A6&"GBP");
 IF(A7="", 0, B7*GOOGLEFINANCE("CURRENCY:"&A7&"GBP"))})

How to calculate total value of a column containing mixed currencies

1 Answers