Sumif only numbers formated as currency

[Guest]

If a column of data consist of currency, % and unformatted numbers, how do
you total only the data formated as currency?

 

[Dave Peterson]

I would insert another column and use it to indicate what's in that other column
in that same row.

I'd use C for currency, % for percent, o for other (or anything you want).

Then you could use:

=sumif(b:b,"c",a:a)

to add all the values in column A that had a C in column B.

 

[Peo Sjoblom]

You could use a help column, assume the values are in A1:A10, insert a new
column B and in B1 put

=LEFT(CELL("format",A1))="C"

copy down to B10

now use

=SUMIF(B1:B10,TRUE,A1:A10)

having said that, it is not a good design to use this method, all it takes
is that
someone changes one format by mistake and your totals will be off

 

[Guest]

Try this small UDF:

Function summ(R As Range) As Double
summ = 0
For Each rr In R
tx = rr.Text
If Left(tx, 1) = "$" Then
summ = summ + rr.Value
End If
Next
End Function


if A1 through A6 contained:

1
2
$3.00
4
$5.00
7

then
=summ(a1:a6) will return 8