Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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?
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)
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.
$and euro signs are just cell formatting information. you can write a long condition to sum cells according to the currency flag.