sum everything but errors

J

Jeffrey

The range I need to sum can sometimes contain references to cells that have
been deleted, creating a formula that looks like =SUM(A1,C4,#REF!,D16:D22)
How can I get the formula to automatically disregard that specific error, and
return the sum of the other cells?
 
G

Gary''s Student

Say we want to sum C1 thru C12, but ignore any errors in that range:

=SUM(IF(ISERROR(C1:C12)=FALSE,C1:C12))

This is an array formula inserted with CNTRL-SHFT-ENTER rather than just ENTER
 
J

Jeffrey

That works if one of the cells in the range contains an error; however, if
the cells in the formula are at random; that is, no defined range, just here
and there, and something gets deleted, then a #ref! shows up in the formula.
In my example below, if the original formula reads:" =SUM(A1,C4,E10,D16:D22)
" and column E gets deleted entirely, the formula will now read "
=SUM(A1,C4,#REF!,D16:D22) " and return a #ref! error as a result. I'm trying
to make that formula dynamic, so that when rows and columns get deleted, the
rest of the formula stays in tact.
 
P

Peo Sjoblom

Since I assume your error comes from a cell I replaced it with E4 but if you
put #REF! in E4 you will see that this works

=SUM(SUMIF(INDIRECT({"A1","C4","E4","D16:D22"}),"<=0"&999^99))



--


Regards,


Peo Sjoblom
 
J

Jeffrey

can I send you an example file?


Peo Sjoblom said:
Since I assume your error comes from a cell I replaced it with E4 but if you
put #REF! in E4 you will see that this works

=SUM(SUMIF(INDIRECT({"A1","C4","E4","D16:D22"}),"<=0"&999^99))



--


Regards,


Peo Sjoblom
Click to expand...
 
Top