#value!

[wal50]

I am using the following which puts a 0 in the field when the lookup finds no
match.
=IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
This works fine except I don't want a 0, I want a blank. But when I use " "
instead of 0, the cell shows as blank but when I sum that column I get #Value!
Any ideas?
Thanks
WAL50

[pdberger]

Wal50 --

Try not using " ", but "". The first puts a space in the cell (you just
can't see it), but the second actually leaves it blank.

HTH

"wal50" wrote:

> I am using the following which puts a 0 in the field when the lookup finds no
> match.
> =IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
> This works fine except I don't want a 0, I want a blank. But when I use " "
> instead of 0, the cell shows as blank but when I sum that column I get #Value!
> Any ideas?
> Thanks
> WAL50

[wal50]

Thanks for the response but "" gave me #value! in the sum calc too.
WAL50

"pdberger" wrote:

> Wal50 --
>
> Try not using " ", but "". The first puts a space in the cell (you just
> can't see it), but the second actually leaves it blank.
>
> HTH
>
> "wal50" wrote:
>
> > I am using the following which puts a 0 in the field when the lookup finds no
> > match.
> > =IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
> > This works fine except I don't want a 0, I want a blank. But when I use " "
> > instead of 0, the cell shows as blank but when I sum that column I get #Value!
> > Any ideas?
> > Thanks
> > WAL50

[Domenic]

Try...

=IF(ISNA(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),"",VLOOKUP($D191,'060
4'!$A$1:$E$141,5,FALSE))

Then use the SUM function to sum. It ignores text values, including
formula blanks. So, for example, you could try something like this...

=SUM(A2:A10)

or

=SUM(A2,A5,A10)

Hope this helps!

In article <95D4EBF8-46E4-4537-96C2-968029ADAB57@microsoft.com>,
wal50 <wal50@discussions.microsoft.com> wrote:

> I am using the following which puts a 0 in the field when the lookup finds no
> match.
> =IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!
> $A$1:$E$141,5,FALSE))
> This works fine except I don't want a 0, I want a blank. But when I use " "
> instead of 0, the cell shows as blank but when I sum that column I get
> #Value!
> Any ideas?
> Thanks
> WAL50

[wal50]

Thanks, but the result is the same. Still get #value! when I sum. The sum
works when I replace with 0.
WAL50

"Domenic" wrote:

> Try...
>
> =IF(ISNA(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),"",VLOOKUP($D191,'060
> 4'!$A$1:$E$141,5,FALSE))
>
> Then use the SUM function to sum. It ignores text values, including
> formula blanks. So, for example, you could try something like this...
>
> =SUM(A2:A10)
>
> or
>
> =SUM(A2,A5,A10)
>
> Hope this helps!
>
> In article <95D4EBF8-46E4-4537-96C2-968029ADAB57@microsoft.com>,
> wal50 <wal50@discussions.microsoft.com> wrote:
>
> > I am using the following which puts a 0 in the field when the lookup finds no
> > match.
> > =IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!
> > $A$1:$E$141,5,FALSE))
> > This works fine except I don't want a 0, I want a blank. But when I use " "
> > instead of 0, the cell shows as blank but when I sum that column I get
> > #Value!
> > Any ideas?
> > Thanks
> > WAL50
>

[Domenic]

Does your column contain a #VALUE! error value?

In article <10F0DDE8-03AB-4EBD-BB77-DE29B2DAE018@microsoft.com>,
wal50 <wal50@discussions.microsoft.com> wrote:

> Thanks, but the result is the same. Still get #value! when I sum. The sum
> works when I replace with 0.
> WAL50

[David Biddulph]

"wal50" <wal50@discussions.microsoft.com> wrote in message
news:95D4EBF8-46E4-4537-96C2-968029ADAB57@microsoft.com...
>I am using the following which puts a 0 in the field when the lookup finds
>no
> match.
> =IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
> This works fine except I don't want a 0, I want a blank. But when I use "
> "
> instead of 0, the cell shows as blank but when I sum that column I get
> #Value!
> Any ideas?

Are you actually summing the column (with the SUM fumction), or are you just
adding the values?
I find that SUM() can cope with blanks, but addition doesn't.
--
David Biddulph

[wal50]

That was it. I was adding (alternate columns); summing solves the problem.
Thank you all for the suggestions.
WAL50

"David Biddulph" wrote:

> "wal50" <wal50@discussions.microsoft.com> wrote in message
> news:95D4EBF8-46E4-4537-96C2-968029ADAB57@microsoft.com...
> >I am using the following which puts a 0 in the field when the lookup finds
> >no
> > match.
> > =IF(ISERROR(VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE)),0,VLOOKUP($D191,'0604'!$A$1:$E$141,5,FALSE))
> > This works fine except I don't want a 0, I want a blank. But when I use "
> > "
> > instead of 0, the cell shows as blank but when I sum that column I get
> > #Value!
> > Any ideas?
>
> Are you actually summing the column (with the SUM fumction), or are you just
> adding the values?
> I find that SUM() can cope with blanks, but addition doesn't.
> --
> David Biddulph
>
>
>