How do I sum different currencies in Google Spreadsheets

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?

• You'll need a flag. The `\$` and euro signs are just cell formatting information. you can write a long condition to sum cells according to the currency flag. – Bibhas Aug 25 '12 at 20:09
• If you find your question to be answered properly, please flag as such. – Jacob Jan Tuinstra Dec 11 '12 at 21:03

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)
``````
• If you find your question to be answered properly, please flag as such. – Jacob Jan Tuinstra Nov 17 '12 at 22:06

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?

SUMIF, like all other worksheet functions (other than, to a limited extent, CELL) cannot access the applied format.

So either GAS or you do indeed manually need to have a flag that specifies the currency for each column.

You can access the euro or dollar format using

``````=IF(LEFT(TO_TEXT(A1:A2))="\$",...
``````

and using that create an Array of conversion products for each, so the entire formula will be something like:

``````=SUMPRODUCT(A1:A24,{IF(LEFT(TO_TEXT(A1:A2))="\$",1, 1.11)})
``````

where the `1.11` is the euro to usd conversion rate. Probably best to use a named range for that.