Using SUM with cells that contain errors
Posted by Keith Lane on February 15, 2002 9:20 AM
I am familiar with using the if iserr function for singular cells. Is there a way to use a range for summing a column where some of the cells may contain errors?
I am trying to sum a column of data, where the cells are populated with the result of a vlookup.
Thanks
K
Posted by Mark W. on February 15, 2002 9:23 AM
=SUMIF(A1:A10,"#N/A")
Posted by Raoul on February 15, 2002 9:43 AM
I am not an Excel wizard like Mark, but if you're looking to add all the non-error cells i.e. excluding the #N/A's, I found that the following array formula seems to work:
=SUM(IF(NOT(ISERROR(A1:A10)),A1:A10,))
(Ctrl-Shift-Enter to get the curlies)
I'm not sure if the NOT/ ISERROR combination could be improved??
As an array beginner I can't explain how to read this formula in 'English' - but it works!
Posted by Mark W. on February 15, 2002 9:49 AM
Oops, typo! Make that =SUMIF(A1:A10,"#N/A")
Posted by Mark W. on February 15, 2002 9:51 AM
I guess it wasn't a typo... this website is eating my < sign again...
Use =SUMIF(A1:A10,"< >#N/A")
...but, when you really use this formula dont'
type a space between < and > like I had to for
this website.
Posted by Keith on February 15, 2002 10:05 AM
Re: Oops, typo! Make that =SUMIF(A1:A10,"#N/A")
Posted by Keith on February 15, 2002 10:10 AM
maybe I am brain dead today?
I tried to use the sumif and even copied your formula and keep getting O.00, am I missing something??
Posted by Mark W. on February 15, 2002 10:19 AM
Did you remove the space character between ? [nt]
Posted by Mark W. on February 15, 2002 10:21 AM
Repost: Did you remove the space character between the less than and greater than signs?
Posted by Keith on February 15, 2002 10:26 AM
Re: Did you remove the space character between ? [nt]
I am in the process of using a sharpie marker to write DUH!!! on my forhead.
It does work, when I follow directions.
This is a lifesaver. Thanks a bunch. : I tried to use the sumif and even copied your formula and keep getting O.00, am I missing something?? :
