SUM values if containing an error

[J.W. Aldridge]

HI.
I have the following formula and I am not getting any results to show.

=SUM(F7,F16,F25,F34,F43)

Each cell in the formula displays the result of another formula (which
may or may not yield a result - if not the result is an value error).

Ie.

F7 = #VALUE!
F16 = #VALUE!
F25 = #VALUE!
F34 = $1300
F43 = #VALUE!



I tried altering =SUM(IF(ISERROR(A2:A10),0,A2:A10)) but could not get
it to work for my situation.

Please help!

 

[Pete_UK]

You should really try to trap your errors at source and not let them
propagate through, so this means amending the formulae which are in
F7, F16, F25, F34 and F43. Whatever formula you have in those cells
should be changed to:

=IF(ISERROR(current_formula),"",current_formula)

This way a blank will be shown rather than the error message. Any
blanks will be ignored in your SUM formula. You could change the ""
for a zero, but this will mess things up if you also want averages
etc.

Hope this helps.

Pete

 

[JE McGimpsey]

The best way would to trap and eliminate the #VALUE! errors, e.g.,
instead of

F7: =F2+F3

use

F7: =IF(COUNT(F2,F3)=2,F2+F3,"")

so that the SUM() will ignore the text. Having users accustomed to
seeing expected error values tends to mask unexpected errors...

If you MUST have "expected" errors, here's one way (array-entered:
CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(ISNUMBER(F7:F43*(MOD(ROW(F7:F43),9)=7)),F7:F43))