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. Aug 25, 2012 at 20:09
• If you find your question to be answered properly, please flag as such. Dec 11, 2012 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)
``````

# Key off the Currency Symbol

The currency symbol, `€` or `\$` in your values, is the first character therefore you can return it using the LEFT function :

``````# A1=\$20.45         # A2=€20.45

=LEFT(A1)           =LEFT(A2)
=LEFT("\$20.45")     =LEFT("€20.45")
="\$"                ="€"
``````

LEFT along with SUMPRODUCT can be used to return the sum of all the values in a range of values, while coverting the currency where necessary.

For example, if your range of currency values is in A1:A2  and `1.11` is the EUR:USD conversion rate :

``````=SUMPRODUCT(A1:A2, IF(LEFT(A1:A2)="\$", 1, 1.11))
# or
=SUMPRODUCT(A1:A2, IF(LEFT(A1:A2)="€", 1.11, 1))
``````

Instead of hardcoding the exchange rate `1.11` , it can be a cell reference or a named range.

For example, where B1 holds the exchange rate :

``````=SUMPRODUCT(A1:A2, IF(LEFT(A1:A2)="€", B1, 1))
``````

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.