10

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?

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

3 Answers 3

8

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)
0
3

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))
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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.