How do I sum currency only numbers in a range?
 

I would like to sum a range of cells, only ignoring non-currency values.
e.g.: 5 10 $4 6 sum = $4

Here's one way to do it -

Assume that the values in your example are in cells A1 through A4, and
with the value '4' formatted as currency. Enter the following formula
in, say, cells B1 and copy it down through cell B4:

=CELL("format",A1)

Note the text value that it returns for the currency-formatted cell. In
a sheet I created that value was "C0" (C and zero), but it might be
different depending on the currency format. Now in a different cell
enter the following formula:

=SUMIF(B1:B4,"C0",A1:A4)

and format that cell accordingly. If you use a consistent currency
format, the SUMIF function plus the CELL("format",) function should
work for you.

Gary

I have tried using cell("format") to do things like that and I would not
recommend it,
I would instead urge the OP to use a better layout, do not mix currency with
other numbers
since it is only the display and not the underlying values that are
different

--
Regards,

Peo Sjoblom


"GaryDK" wrote in message
ups.com...
Here's one way to do it -

Assume that the values in your example are in cells A1 through A4, and
with the value '4' formatted as currency. Enter the following formula
in, say, cells B1 and copy it down through cell B4:

=CELL("format",A1)

Note the text value that it returns for the currency-formatted cell. In
a sheet I created that value was "C0" (C and zero), but it might be
different depending on the currency format. Now in a different cell
enter the following formula:

=SUMIF(B1:B4,"C0",A1:A4)

and format that cell accordingly. If you use a consistent currency
format, the SUMIF function plus the CELL("format",) function should
work for you.

Gary