How to sum a column with errors
 

I have a column where the cells contain a vlookup formula for an exact match
only. If there is no match, the value N/A# is returned. However, I would
like to be able to sum the values that the vlookup does find.

How do you write the formula so that instead of returning N/A# it returns
zero or blank if the vlookup can't find a match?

Thanks

How to sum a column with errors
 

Best way

change the vlookup formula to

=IF(ISNA(Vlookup_formula)),0,vlookup_formula)

that way you will get 0 instead of #N/A

otherwise

=SUMIF(Range,"<#N/A")

will sum the non error values


--
Regards,

Peo Sjoblom


"blkane" wrote in message
...
I have a column where the cells contain a vlookup formula for an exact
match
only. If there is no match, the value N/A# is returned. However, I would
like to be able to sum the values that the vlookup does find.

How do you write the formula so that instead of returning N/A# it returns
zero or blank if the vlookup can't find a match?

Thanks

How to sum a column with errors
 

Use the ISNA function inside of an IF Statement:

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

HTH,
Elkar


"blkane" wrote:

I have a column where the cells contain a vlookup formula for an exact match
only. If there is no match, the value N/A# is returned. However, I would
like to be able to sum the values that the vlookup does find.

How do you write the formula so that instead of returning N/A# it returns
zero or blank if the vlookup can't find a match?

Thanks

How to sum a column with errors
 

Peo,

Thanks, I was reading some other threads at the same time you posted. I
knew there had to be a way. I'm going to go with the ISNA function. This
series of functions is new to me but I plan to use them more in the future.


"Peo Sjoblom" wrote:

Best way

change the vlookup formula to

=IF(ISNA(Vlookup_formula)),0,vlookup_formula)

that way you will get 0 instead of #N/A

otherwise

=SUMIF(Range,"<#N/A")

will sum the non error values


--
Regards,

Peo Sjoblom


"blkane" wrote in message
...
I have a column where the cells contain a vlookup formula for an exact
match
only. If there is no match, the value N/A# is returned. However, I would
like to be able to sum the values that the vlookup does find.

How do you write the formula so that instead of returning N/A# it returns
zero or blank if the vlookup can't find a match?

Thanks

How to sum a column with errors
 

I tried that and I kept getting a formula area that would point directly to
the double "" in the formula. I finally figured out that I was missing a
parens just before the ,"".

You both have been very helpful. Thanks



"Elkar" wrote:

Use the ISNA function inside of an IF Statement:

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

HTH,
Elkar


"blkane" wrote:

I have a column where the cells contain a vlookup formula for an exact match
only. If there is no match, the value N/A# is returned. However, I would
like to be able to sum the values that the vlookup does find.

How do you write the formula so that instead of returning N/A# it returns
zero or blank if the vlookup can't find a match?

Thanks

How to sum a column with errors
 

It's better to fix the #N/A errors but sometimes you may want to know if
there is an error.

This will sum a range and ignore any #N/A errors:

=SUMIF(A1:A10,"<#N/A")

Biff

"blkane" wrote in message
...
I have a column where the cells contain a vlookup formula for an exact
match
only. If there is no match, the value N/A# is returned. However, I would
like to be able to sum the values that the vlookup does find.

How do you write the formula so that instead of returning N/A# it returns
zero or blank if the vlookup can't find a match?

Thanks