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

=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

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

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 ")

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 ")

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.

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.

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?

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?