embedding "ISERROR" function into an "IF" statement

M

michael

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
 
B

Bob Phillips

=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)
 
D

Dave Peterson

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>.
 
H

Harlan Grove

[email protected] 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?
Click to expand...

Don't bother with ISERROR (or ISNUMBER) or IF. Just use

=SUMIF(YourRangeHere,"<0")+SUMIF(YourRangeHere,">0")
 
B

Bob Phillips

Hardly intuitive.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
B

Bob Phillips

intuitive, obtained through intuition rather than from reasoning or
observation
 
H

Harlan Grove

Bob Phillips wrote...
intuitive, obtained through intuition rather than from reasoning or
observation
Click to expand...
....

Intuition is independent of experience and education?

Intuition is preferable to reasoning when it comes to writing
spreadsheet formulas?
 
B

Bob Phillips

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