Summing values without N/A (2000)

Topic

New Reply

I think I have had a formula regression or reversion back to a pre-brain functioning period and I cannot, for the life of me, figure out how to sum the cells in B4:B13 where the contents are not “#N/A”

If have tried at least 15 different versions of =IF(NOT(ISNA(B4:B13)),SUM(B$:B13))) and other such. Any help?

Reply | Quote

Replies

One possible solution
=SUMIF(B4:B13,”>0″)+SUMIF(B4:B13,”<0")

Reply | Quote

Mike

Try this:

=SUMIF(B4:B13,”#N/A”)

Brent

Reply | Quote

Thanks, both of those look like they should work…guess it was too late last nite (or too early this morning I should say)!

Reply | Quote

More general approach avoiding all errors is to use an array formula:

=SUM(IF(ISERROR(Range),””,Range))

Which of course can easily be adapted to:

=AVERAGE(IF(ISERROR(Range),””,Range))
=STDEV(IF(ISERROR(Range),””,Range))

All Array entered with control-shift-enter.

Reply | Quote