Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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!
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.
How it works:
Ctrl+F3 & Click New.Cell_Format in Name Box.Enter this in Refers To Box.
=GET.CELL(53,Sheet1!$B2)
N.B. Sheet1!$B2 is editable.
Write this Formula in Cell C2 & fill down.
=LEFT(Cell_Format,1)
Enter $, ₹ & £ symbols in Cell D2, D3 & D4.
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!
