Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#REF! errors | Excel Discussion (Misc queries) | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
#Value! errors | New Users to Excel | |||
IIf and value-errors | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |