sum everything but errors
 

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?

sum everything but errors
 

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?

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?

sum everything but errors
 

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


"Jeffrey" wrote in message
...
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?

sum everything but errors
 

can I send you an example file?


"Peo Sjoblom" wrote:

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


"Jeffrey" wrote in message
...
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?

sum everything but errors
 

Nevermind; it works! wow; Thanks!

"Jeffrey" wrote:

can I send you an example file?


"Peo Sjoblom" wrote:

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


"Jeffrey" wrote in message
...
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?