Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

IF(ISNA(YourLookupFormula),0,YourLookupFormula)

Vaya con Dios,
Chuck, CABGx3



"marketingman" wrote:

Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

Try this:

If(isna(vlookup(your lookup)),0,vlookup(your lookup))

Hi,

To sum the values ignoring the error values, you may want to use the
following formula. You need not alter your vlookup formula

In range A1:A4, you have the following

1
2
3
#DIV/0!

In cell A6, array enter (Ctrl+Shift+Enter) the following formula

SUM(IF(NOT(ISERROR(A1:A4)),A1:A4))

Regards,

Ashish Mathur


"marketingman" wrote:

Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

1.

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

2. If you're sending the workbook across countries...

=SUMIF(Range,"<"&NA())

marketingman wrote:
Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.