Prev: Highlight cell colour
Next: How to expand a collapsed column on excel worksheet?
From: Timothy Millar on 10 May 2010 15:00
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.
From: JLatham on 10 May 2010 15:13
Wrap your VLOOKUP() formula in a 'error trap' that returns 0 instead of #N/A.
As:

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


"Timothy Millar" wrote:

> 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.
From: Gord Dibben on 10 May 2010 16:02
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

On Mon, 10 May 2010 12:00:03 -0700, Timothy Millar
<TimothyMillar(a)discussions.microsoft.com> wrote:

>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.

 | 
Pages: 1
Prev: Highlight cell colour
Next: How to expand a collapsed column on excel worksheet?