1
1
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.
1
1
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.
2
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.
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 always2
... – lorenzo-s – 2012-02-13T12:44:14.163you 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 – 2012-02-13T13:04:51.870
I'm using € and HKD. – lorenzo-s – 2012-02-13T13:38:06.337
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 usedMid(cell.Text, 2, 1)
instead ofLeft(cell.Text, 1)
in the VBA code, and I solved. Thank you. – lorenzo-s – 2012-02-13T13:56:32.750Glad you got it figured out! – Raystafarian – 2012-02-13T14:31:03.933