Sum of different currencies Excel

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

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.

4. Write this Formula in Cell C2 & fill down.

   =LEFT(Cell_Format,1)
   

5. Enter $, ₹ & £ symbols in Cell D2, D3 & D4.

6. Write this formula in Cell C8 & fill down.

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

Answer 4

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) |

Answer 5

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.