sum everything but errors
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.
"Gary''s Student" wrote:
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
--
Gary''s Student - gsnu200755
"Jeffrey" wrote:
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?
|