Google Sheets – Convert Currency to USD Based on Currency Shown in Cell

formulasgoogle sheets

I use a Google Sheet to track ebook sales, some of which are international. Column H below has the royalty amount that was paid out for the sale, but that amount is still shown in the sale currency, as seen in Column F. I'd like to be able to convert the royalty amount to USD using a formula. I found one formula that comes close to what I want…it takes the money amount shown in a cell (e.g. A2) and converts it from one currency to another (GBP to USD in this case).

=A2*GOOGLEFINANCE("CURRENCY:GBPUSD")

However, this formula obviously requires you to enter both the base currency and the conversion currency, and I'd have to change it every time I needed to convert something besides GBP. Since I'll always be converting the sale currency to USD, is there a way to write a similar formula that takes the amount from Column H, looks at the currency listed in Column F, and converts that to USD?

enter image description here

Best Answer

Please use the following.

=IF(LEN(A2),IFERROR(D2*GOOGLEFINANCE("CURRENCY:"&B2&"USD"&""),D2),)

Copy down till it meets your needs.

enter image description here

Functions used:

Related Topic