Excel – Sum of different currencies Excel

microsoft excelmicrosoft-excel-2013

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:

enter image description here

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 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
Related Question