sum everything but errors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
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
 
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...
 
Back
Top