MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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,"&LT; &GT;#N/A")

...but, when you really use this formula dont'
type a space between &LT; 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?? :