6

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?

  • 1
    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
6

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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