8

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

7

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)
1
  • If you find your question to be answered properly, please flag as such. Nov 17, 2012 at 22:06
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.

1
1

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.

Your Answer

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

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