I have a drop down menu that I use for parts and pricing. The problem I have
is on my quote sheet it won't let me add up all of my pricing because not all
fields are filled in some are blank and when they are blank the total is #N/A
not allowing you to add up #N/A mixed with numbers or it will come up #N/A.

=VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)

Is there a way an emty cell can be equal to zero without having to add and
select zero from a drop down menu giving the zero a $.00 cost.
I hope you can understand what i am doing. Thanks

Use this=

IF(ISERROR(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALS E)),0,(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)))

That way if it would normally have an error it will convert it to 0.


Ashley

" wrote:

I have a drop down menu that I use for parts and pricing. The problem I have
is on my quote sheet it won't let me add up all of my pricing because not all
fields are filled in some are blank and when they are blank the total is #N/A
not allowing you to add up #N/A mixed with numbers or it will come up #N/A.

=VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)

Is there a way an emty cell can be equal to zero without having to add and
select zero from a drop down menu giving the zero a $.00 cost.
I hope you can understand what i am doing. Thanks

Ashley

ISERROR will mask all errors.

Better to trap for NA only

See Biff's post for ISNA function


Gord Dibben MS Excel MVP

On Wed, 3 Sep 2008 14:16:05 -0700, Ashley1432
wrote:

Use this=

IF(ISERROR(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FAL SE)),0,(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)) )

That way if it would normally have an error it will convert it to 0.


Ashley

" wrote:

I have a drop down menu that I use for parts and pricing. The problem I have
is on my quote sheet it won't let me add up all of my pricing because not all
fields are filled in some are blank and when they are blank the total is #N/A
not allowing you to add up #N/A mixed with numbers or it will come up #N/A.

=VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)

Is there a way an emty cell can be equal to zero without having to add and
select zero from a drop down menu giving the zero a $.00 cost.
I hope you can understand what i am doing. Thanks

You can use something like this that will ignore the errors:

=SUMIF(A1:A10,"<1E100")

However, you will probably be better off preventing those errors in the
first place:

=IF(ISNA(VLOOKUP(SHELVING!J22,Cost!A1:B16,2,0)),0, VLOOKUP(SHELVING!J22,Cost!A1:B16,2,0))



--
Biff
Microsoft Excel MVP


" wrote
in message ...
I have a drop down menu that I use for parts and pricing. The problem I
have
is on my quote sheet it won't let me add up all of my pricing because not
all
fields are filled in some are blank and when they are blank the total is
#N/A
not allowing you to add up #N/A mixed with numbers or it will come up
#N/A.

=VLOOKUP(SHELVING!J22,Cost!A1:B16,2,FALSE)

Is there a way an emty cell can be equal to zero without having to add and
select zero from a drop down menu giving the zero a $.00 cost.
I hope you can understand what i am doing. Thanks