# Sum of different currencies Excel

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!

• It may be easiest to have a separate column that denotes the currency. Sum up with conditions based on grouping by that column. – kobaltz Jan 4 '14 at 2:41

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 from `B2`, 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:

``````Function GetFormat(r As Range)
Dim arr() As String
ReDim arr(1 To r.Cells.Count)
i = 1
For Each c In r
arr(i) = c.NumberFormat
i = i + 1
Next c
GetFormat = WorksheetFunction.Transpose(arr)
End Function
``````

There are two ways you can do it. I recommend that you separate out the currency sign and number so for example £100 is written as 100 in in cell and £ in the adjacent cell. Then you may have below it 50e (euro) written as 50 and e and on.

The first method involves using pivot tables - very simple.

But even simpler and the preferred way is as follows

In range d2 to d19 we have amounts (just numbers) and in range c2 to c19 we have the symbols like e or £ or \$ etc

In a separate cell where I want to show the total of e, I enter:

=SUMIF(D2:D19,"e",C2:C19)

Next to it I want to sum the £, so I enter:

=SUMIF(D2:D19,"£",C2:C19)

It works like a dream.

• I have just noticed a similar answer by Ivan Chau (doh!) - but maybe a different explanation might help! – Athar Siddiqui Sep 12 '14 at 7:17 How it works:

1. Press `Ctrl+F3` & Click `New`.
2. Write `Cell_Format` in `Name Box`.
3. Enter this in `Refers To Box`.

`````` =GET.CELL(53,Sheet1!\$B2)
``````

N.B. `Sheet1!\$B2` is editable.

1. Write this Formula in Cell `C2` & fill down.

``````=LEFT(Cell_Format,1)
``````
2. Enter \$, ₹ & £ symbols in Cell `D2, D3 & D4`.

3. Write this formula in Cell `C8` & fill down.

``````=SUMIF(\$C\$2:\$C\$6,D2,\$B\$2:\$B\$6)
``````

It seems difficult to identify the currency format by using solely excel formula.
Even `=CELL("format",B2)` is not useful.

Similar to @kobaltz idea, this method needs an extra column and is error-prone.

The additional Currency column `C:C` can either be manually inserted or selected from drop-down list with custom values "US" and "YAN".

| Dollars | `=SUMIF(C:C,"US",B1:B3)` |
| Yan | `=SUMIF(C:C,"YAN",B1:B3)` |

In range B2 to B4 we have amounts (just numbers, 10.00, 20.00 and 30.00 ) and in range C2 to C4 we have the symbols (\$,Yan,\$)

In a separate cell we want to show the total of \$, I enter: =SUMIFS(B2:B4,C2:C4,C5) C5 cell is fill with "\$"

Next to it I want to sum the Yan , so I enter: =SUMIFS(B2:B4,C2:C4,C6) C6 cell is fill with "Yan"

It works.