# 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.

-

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

-
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