Summing #N/A occurrences

[AtTheEndofMyRope]

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.

 

[Mike H]

Hi,

To eliminate NA# use this format for your vlookup

=IF(ISNA(VLOOKUP(J1,A1:B11,2,FALSE)),0,VLOOKUP(J1,A1:B11,2,FALSE))

Mike

 

[AtTheEndofMyRope]

That works. Thanks. This is what I used:

=IF(ISNA(VLOOKUP(B33,Week17!K2:K17,1,FALSE)),0,1)


Now, I need to also nest that in a loop to test for a blank cell,and
return a blank for empty cells, which is different than a failed vlookup
reference, so the blank cell check should be the first loop.

The NA test finds out if it was a winner, as a lack of an #N/A on the
vlookup represents a "hit" in the win column, Unless there was a bye or
the game has not been played yet.

That keeps games that have not yet been played from showing up as a
loss.

These are 17 weeks worth of 16 game per sheet weekly sheets.

Some teams have a bye, which are listed at another place on the sheet,
so I will eventually loop through that location as well, so that a bye
does not show up as a win or loss.

The result will be a sheet with all 32 teams on it that shows wins and
losses compiled from the sheets which represent each week of gameplay.

I am pretty close. I have found that summing zeros fails, and use
countif to tally losses.

As the weekly scores get filled in, those cells become no longer blank.

There are only a few weeks that carry a bye.

Almost done. Thanks for your help(s).

 

[T. Valko]

=IF(ISNA(VLOOKUP(B33,Week17!K2:K17,1,FALSE)),0,1)

Sounds like you're doing something with the NFL season. So, a team can only
appear once per week.

This will do the same thing as the above formula:

=COUNTIF(Week17!K2:K17,B33)

 

[AtTheEndofMyRope]

Sounds like you're doing something with the NFL season. So, a team can only
appear once per week.

This will do the same thing as the above formula:

=COUNTIF(Week17!K2:K17,B33)

B33 is on a local sheet, and does not get filled, it gets referenced
to. It is a team city/state name.

IF it fails to get found in the array, the "#N/A" return gets tallied as
a loss. Otherwise a win is assumed.

Actually, I am tallying a 17 week / sheet array.

So, each week shows the team name of the winning team of a given game,
based on the scores entered in that week's score keeping cells.

Since the value is a text string, I count a 1 if it is present, as that
would represent a win.

I instead count the "#N/A" occurrences for losses, but assume that if
"#N/A" is not found, it MUST be a win.

The problem is when there is a bye, but that is a simple added loop
which I should put in front of only those weeks where a bye applies.

Otherwise, I only check for the N/A on the vlookup, and assume a loss
if found, and a win if not.

The teams sheet has an array that has these logical results in a 17 by
16 cell array. There is a wins column and a losses column that shows the
counts of "1" and "0" in the array.

So, IN the array, I should test for presence in the bye area on those 5?
weeks. Otherwise, I only test for the N/A, and make assumptions based on
that. I count 1s with =SUM and I count the zeros with =COUNTIF. Seems to
show wins and losses pretty well.

In the individual week sheets, where the game scores are stored, and
the team name of the winning team gets filled in, I have an isblank loop
to keep the field blank if there are no scores entered.

That way, my teams sheet can check for a blank as well, return no value
to the sum or countif cells, which should keep the wins and losses
columns correct

 

[Chip Pearson]

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)