# 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. Jan 4, 2014 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! Sep 12, 2014 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.

I wanted to figure out the total # of all currencies as a value not for example:

US 1 / EUR 1.1 / CAD 1.4 / BOL 700,000 / RMB 8 /

to get a final official total of numbers divided by total contributors countries. sO if the total countries # value in market officially is 112890 and we divide by 200 countries contributed we would get value of 564.45. Then move on with other information of population, total official currency in cicurlation, adding crypto and metals etc would be a nice data set to look into.

This would be a nice number to see from a different perspective with live updates. with total changes and updated for the world to see its growth and changes. If anyone can help would be awesome!