Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have this situation in Microsoft Excel:

Data

Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by currency. Is that possible? How?

Thanks.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

VBA

Because you used the "format" option to format these cells, formatting is not contained within the value of the cell, so we will need to define a function in VBA. Put this (source) in a new module:

Public Function sumFormats(rng As Range) As String
Application.Volatile
Dim cell As Range, dblDollar#, dblPound#
dblDollar = 0: dblPound = 0
For Each cell In rng
If Len(cell.Value) > 0 Then
If Left(cell.Text, 1) = "$" Then
dblDollar = dblDollar + cell.Value
Else
dblPound = dblPound + cell.Value
End If
End If
Next cell
sumFormats = "Sum of currency: $" & dblDollar & "; Sum of Pounds: " & ChrW(163) & dblPound
End Function

Then use the new function =sumFormats(A1:A20) for whatever cells you want and it will give you the sums on 1 line. If you need subtotals on different lines, we will need to modify the output of the function.


Non-VBA

If you don't want to mess with VBA, you will need a helper column next to your numbers. In that column use the function =cell("format",A1) and this will return ,2 for £ and C2 for $. Then run a =sumif function for the subtotals.

=sumif(helpcolumncell,"C2",currencycell) to sum for dollars, and replace C2 with ,2 for pounds. You can always hide the helper column if you don't like it. In this case we'll assume your data is in Column B and helper is Column C.

A             B    C

             Data  Format
              $1    =cell("format",B2) = C2
              £2    =cell("format",B3) = ,2  
              £3    =cell("format",B4) = ,2
              $4    =cell("format",B5) = C2 
sum dollar =sumif($C$2:$C$4,"C2",$B$2:$B$4) = 5

sum pound  =sumif($C$2:$C$4,",2",$B$2:$B$4) = 5

Then you will need to format these sumif cells to get the currency sign, or add in a =concatenate("$"&sumif(...)) function

share|improve this answer
    
I tried both methods. With VBA I always end up in the ELSE block (pound) for every cell. Using the non-VBA method, =cell("format",A1) returns always 2... –  lorenzo-s Feb 13 '12 at 12:44
    
you changed the cell ranges to your ranges right? And you used per-defined currency formats? These both work when I use them, let's figure out the difference –  Raystafarian Feb 13 '12 at 13:04
    
I'm using € and HKD. –  lorenzo-s Feb 13 '12 at 13:38
    
I don't know why, but Excel places an empty space (ASCII 32) before currency symbol. So, cell values are something as ␢€␢1234.56. So I used Mid(cell.Text, 2, 1) instead of Left(cell.Text, 1) in the VBA code, and I solved. Thank you. –  lorenzo-s Feb 13 '12 at 13:56
    
Glad you got it figured out! –  Raystafarian Feb 13 '12 at 14:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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