Counting numeric but not Alpha

[Timothy Millar]

I have three columns and two rows (to make this easy). The first row of
cells are blank. The second row uses a VLOOKUP that pulls a number based on
a name entered into the first row. The first two columns have a name but the
third is left blank (example I am looking up only two names at the time and
not three). I want to add the numbers in the second row but if there is no
name in the first row the second row shows as #N/A because the VLOOKUP is
pulling no data at the moment. I want to sum up the numbers pulled by the
VLOOKUP but the formula I have also counts the #N/A which I want it to ignore.

Any suggestions? Let me thank you ahead of time.

 

[JLatham]

Wrap your VLOOKUP() formula in a 'error trap' that returns 0 instead of #N/A.
As:

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

 

[Gord Dibben]

Change your VLOOKUP formulas to trap for #N/A

=IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE))

Excel's SUM ignores the "" returned from the ISNA trap.


Gord Dibben MS Excel MVP