5

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!

1
  • 1
    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, 2014 at 2:41

6 Answers 6

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
1

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.

1
  • 1
    I have just noticed a similar answer by Ivan Chau (doh!) - but maybe a different explanation might help! Sep 12, 2014 at 7:17
1

enter image description here

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

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

0

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.

0

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!

2
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 24 at 15:02
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review May 24 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.