Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I'm trying to add up a column of numbers and some of the cells have
#N/A or #DIV/0! which causes my summation to come up as an error as well. I'd like to keep my #N/A's in the cells and use the ISERROR function and thought it should be embedded into an IF statement. Can any help me do this? Thanks Mike |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
=SUM(IF(NOT(ISERROR(C3:C31)),C3:C31))
as an array formula, commit it with Ctrl-Shift-Enter, not just Enter -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... I'm trying to add up a column of numbers and some of the cells have #N/A or #DIV/0! which causes my summation to come up as an error as well. I'd like to keep my #N/A's in the cells and use the ISERROR function and thought it should be embedded into an IF statement. Can any help me do this? Thanks Mike |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Alternative to Bob's function:
=sum(if(isnumber(C3:C31),C3:C31)) Still array entered. It doesn't use =iserror(), but I think it's easier to understand <bg. wrote: I'm trying to add up a column of numbers and some of the cells have #N/A or #DIV/0! which causes my summation to come up as an error as well. I'd like to keep my #N/A's in the cells and use the ISERROR function and thought it should be embedded into an IF statement. Can any help me do this? Thanks Mike -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hardly intuitive.
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Harlan Grove" wrote in message ups.com... wrote... I'm trying to add up a column of numbers and some of the cells have #N/A or #DIV/0! which causes my summation to come up as an error as well. I'd like to keep my #N/A's in the cells and use the ISERROR function and thought it should be embedded into an IF statement. Can any help me do this? Don't bother with ISERROR (or ISNUMBER) or IF. Just use =SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere,"0 ") |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Bob Phillips wrote...
Hardly intuitive. .... "Harlan Grove" wrote in message .... =SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere," 0") Depends on one's understanding of how SUMIF works. SUMIF with <, <=, , = numeric comparisons only sums numbers and excludes error values. In a similar way, COUNTIF can count the instances of particular error values, e.g., =COUNTIF(A:A,"#N/A"). Once one learns how COUNTIF and SUMIF works, maybe then it becomes intuitive. |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
intuitive, obtained through intuition rather than from reasoning or
observation "Harlan Grove" wrote in message ups.com... Bob Phillips wrote... Hardly intuitive. ... "Harlan Grove" wrote in message ... =SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere, "0") Depends on one's understanding of how SUMIF works. SUMIF with <, <=, , = numeric comparisons only sums numbers and excludes error values. In a similar way, COUNTIF can count the instances of particular error values, e.g., =COUNTIF(A:A,"#N/A"). Once one learns how COUNTIF and SUMIF works, maybe then it becomes intuitive. |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Bob Phillips wrote...
intuitive, obtained through intuition rather than from reasoning or observation .... Intuition is independent of experience and education? Intuition is preferable to reasoning when it comes to writing spreadsheet formulas? |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Now you are just twisting what has been said. Pray point to the part where I
said that intuition is preferable to reasoning ... I said, hardly intuitive. The word was carefully chosen, and by the definition of intuitive, it wasn't, it isn't, it never will be, regardless of experience or education. That formula is not intuitive, I won't twist anything just quote you, 'Once one learns how COUNTIF and SUMIF works', that is not intuitive. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Harlan Grove" wrote in message .com... Bob Phillips wrote... intuitive, obtained through intuition rather than from reasoning or observation ... Intuition is independent of experience and education? Intuition is preferable to reasoning when it comes to writing spreadsheet formulas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can an "IF()" statement contain a print function | Excel Discussion (Misc queries) | |||
Combining an If Statement with NETWORKDAYS function | Excel Discussion (Misc queries) | |||
IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
Combining SUM Function with Nested If Statement | Excel Discussion (Misc queries) | |||
How to resize a comment box, by embedding code into a function? | Excel Worksheet Functions |