Google Sheets – Sum Different Currencies

google sheets

I have a spreadsheet where some columns are formatted in EUR, and some in USD. I would like to be able to sum them by taking into consideration the exchange rate. Example:

A1 = $10, A2 = €10

A3 = A1 + A2 ==> $10 + $(10*1.24) = $22.4

So basically, can I use SUMIF() on a cell formatting condition, or do I manually need to have a flag that specifies the currency for each column?

Best Answer

If you set the B-column to be the exchange rate, you can use the sumproduct formula like this, with the following data set:

A1=$10, A2=€10, B1=1.24, B2=1

=SUMPRODUCT(A1:A2;B1:B2)

Related Solutions

Google Sheets – How to Use INDEX or OFFSET Functions

The answer is this:

INDEX(RANGE,ROW,COL)

For example:

INDEX(B2:K4,1,5) the range has three rows (From 2 to 4). The 1 is row 1 within the range which is row 2 of the whole spreadsheet. The 5 is column 5 within the range which corresponds to Column F (the 6th column of the spreadsheet).

Google Sheets – Calculate Sum of Two Columns Using Max from Each Row

In your particular example, you can use a SUMPRODUCT formula.

=SUMPRODUCT((A1:A5*(A1:A5>=B1:B5)*(C1:C5="Yes"))+(B1:B5*(B1:B5>A1:A5)*(C1:C5="Yes")))

This approach may be clumsy in your real world example, though. If your real data is more complicated, post a snippet that shows that complication and I can take a more advanced crack at it.

Best Answer

Related Solutions

Google Sheets – How to Use INDEX or OFFSET Functions

Google Sheets – Calculate Sum of Two Columns Using Max from Each Row

Related Topic