sum a range of cells that include an error

[Guest]

How do I sum a range of cells that include a #N/A error. I want the #N/A
cells to register as 0. Currently the total returned is also #N/A.
Thanks

 

[Biff]

Hi!

Several options:

Fix the formula that returns #N/A.

=IF(ISERROR(your_formula),0,your_formula))

or

=IF(ISERROR(your_formula),"",your_formula))

If you use either of the above then a simple =SUM() will
now work.

If you want the #N/A error to be displayed then:

=SUMIF(A1:A10,">0")

Biff

 

[Bob Phillips]

I think you want

=SUM(IF(NOT(ISERROR(A1:A100)),A1:A100)

which is an error, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)