worksheet function - Filtering by currency in Excel

I have this situation in Microsoft Excel:

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.

Answer

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