There are several things you can do. You can change your VLOOKUP
formulas to something like

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

The problem with this approach is that under most circumstances, the
VLOOKUP is called twice, first within the ISNA function and if it
doesn't return #N/A, it is called a second time. This duplication can
cause performance issues.

In Excel 2007, the IFERROR function was introduced, which allows you
to specify a value to return if a formula returns an error. E.g,

=IFERROR(VLOOKUP(...),0)

If VLOOKUP is successful, its result is returned. If VLOOKUP returns
an error, the result is 0. This function is available only in 2007 and
later. It doesn't exist in 2003 and previous.

The other approach is to leave your VLOOKUPs unchanged and write the
SUM formula to ignore #N/A errors. E.g., if you have your VLOOKUPs in
A1:A10, you can use the following array formula:

=SUM(IF(ISNA(A1:A10),0,A1:A10))

If you just want to count the non-N/A cells, use either of the
following array formulas. The produce the same result:

=SUM(IF(ISNA(A1:A10),0,1))
=SUM(--NOT(ISNA(A1:A10)))

Since these are Array Formulas, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 30 Aug 2009 11:28:56 -0700, AtTheEndofMyRope
wrote:

I have a vlookup that returns a logical 1 if a value is present. If it
is not present, the lookup fails and I get an #N/A back.

So where I count up the 1s is fine, but I am having problems counting
up the number of "#N/A" occurrences I have in that row. It returns
"#N/A".

So, I would like to either nest the Vlookup in an IF so that I get back
a 0 when the lookup fails to find the referred search term, OR I would
like to simply tally the number of "#N/A" occurrences in that row.

These fail:

=SUMIF(L2:AB2,(ISNA))

This works, but isn't really what I want... I think.

=COUNTIF(L2:AB2,"#N/A")


I'd rather correct the errant return from the vlookup to give a 0 when
it fails to find the reference value.