Sum Function with Error values

M

Mizan.Howlader

Hi,

Can anyone please let me know how can I add some values in a column
which contains some #N/A.

Thanks.

MH
 
J

JE McGimpsey

Good practice would generally recommend that you eliminate the #N/As,
instead, to avoid having "expected" errors. If they're being generated
from a lookup, you can use something like

=IF(ISNA(VLOOKUP(...)),"Not Found",VLOOKUP(...))

in which case SUM will ignore the text.

You can, though, use this array-entered (CTRL-SHIFT-ENTER or CMD-RETURN)
formula:

=SUM(IF(ISNA(A1:A50),0,A1:A50))
 
J

joeu2004

Can anyone please let me know how can I add some values in a column
which contains some #N/A.
Click to expand...

One way: the following array formula (commit with ctrl-shift-Enter,
not just Enter):

=sum(if(iserror(A1:A10),0,A1:A10))
 
J

joeu2004

Better to use ISNA() than ISERROR() to avoid masking other errors.
Click to expand...

You are correct that ISNA() fits a literal reading of the OP's
inquiry. But I am reasonably sure that if the OP wants to avoid #N/A
errors, the OP probably wants to avoid all errors, without caring
which one it is. The other errors will not be masked. They will
appear exactly where they belong, namely in the cells that cause any
error initially. The OP simply wants to avoid propagating errors that
the OP has chosen to tolerate. Using ISERROR() is more flexible in
that respect.

Of course, that is merely my opinion and my interpretation of the
inquiry. It is just as valid or invalid as your interpretation.
Either or us could be equally wrong or right.
 
J

JE McGimpsey

I don't know what you're basing being "reasonably sure" on, but it's a
bad idea, even if the OP thinks he wants it.

Having audited hundreds of complex workbook systems, I can say with
reasonable authority that using ISERROR() is near the top of the list
for generating erroneous results, leading to bad decisions based on
those results. I certainly wasn't able to infer as much of the OP's
intent from his single sentence as you were, but IMO having a dependent
function return a valid value when an unexpected error occurs in its
arguments is a first-class design foul-up.

For better or worse (mostly worse), XL returns #N/A as an "expected
error" for some functions, e.g., VLOOKUP. Trapping it with ISERROR()
means that if a precedent calculation returns, say, #DIV/0 due to an
invalid or unanticipated condition, that THAT error will be ignored,
and, depending on how the data is laid out, it may *not* be clearly
exposed (and you just have to read these groups to see how many people
*don't* check their data first when they get an error). The probability
of missing the error in the precedent cells approaches 1 as the amount
of data increases.

That's why I fully expect XL2007's new IFERROR() function to lead to
lowered reliability. It's great when used to flag an unexpected error.
It will lead to wrong results when used to mask them.

And, as you said, that's my opinion.
 
J

Jerry W. Lewis

For the special case of SUM, 0 can replace #N/A values. More generally the
array formula
=SUM(IF(ISNA(A1:A50),"",A1:A50))
would still work if SUM were replaced with a different function.

Jerry
 
Top