Google Sheets – sum column with different currencies

So I have column A with amount (integer), and column B with currency (USD, GBP, EUR…).

Ideally I want some cell to sum the whole column, converted into USD, taking into account the differenct currencies. I know I can do this by splitting it into different cells and steps (i.e. different cell for each currency etc.), but I was wondering if a formula can be concocted for this.

Also I just learned now about GOOGLEFINANCE which is awesome, I’m just not sure how to put it all together.

Edit: According to this answer, I can add a helper column. I was wondering if there’s any way not to add one.

>Solution :

My friend you can create a Google Sheet with sample data.

Here are the steps below:

1. Create the Google Sheet:

• Open Google Sheets.
• Create a new, blank worksheet.

2. Input the Data:

• In column A, enter the amounts.
• In column B, enter the corresponding currencies like USD, EUR, GBP, etc.

3. Insert the Formula:

• In a cell where you want the total, probably C1, you can insert the formula.

4. Test the Formula:

• After inserting the formula, you should see a total in the cell where you put the formula.
• You can test the accuracy by manually calculating one or two conversions using current exchange rates and comparing them to the formula’s output.

Example Sheet:

Here is what your sheet might look like:

Where YOUR_FORMULA Could Be:

=SUM(
 ARRAYFORMULA(
   IF(B2:B = "USD", A2:A, 
   IF(B2:B = "EUR", A2:A * GOOGLEFINANCE("CURRENCY:EURUSD"), 
   IF(B2:B = "GBP", A2:A * GOOGLEFINANCE("CURRENCY:GBPUSD"), 
   0))))
)

5. Review Results:

• After you enter the formula, the cell should display the total converted to USD.
• Make sure that the conversion rates used by GOOGLEFINANCE are up to date or have minor delays. You can check the conversion rates from other financial news websites to verify the accuracy.

By following these steps and using the example, you should be able to test and ensure the formula’s functionality and accuracy.