I have an excel workbook which requires me to calculate the sum of different currencies on the same column, here is the result I'm looking for:
The $40.00
in Dollars
was calculated based on item A and C since their currency are the same, while $20.00
in Yen
is the total for items that has the currency of Yen
. Is this possible? Please help, thanks!
Best Answer
If you are open for user defined function, try my approach:
suppose your values are in
B2:B4
range and you need to calculate total sum of currency fromB2
, than you can use array formula:=SUMPRODUCT(IF(GetFormat(B2:B4)=GetFormat(B2),1,0),B2:B4)
Note, that it is an array formula, so you need to press CTRL+SHIFT+ENTER
But before you should add user defined function to your workbook to get array of range numberformats: