Lookup and #N/A
 

I am trying to create an inventory list that is blank unless someone enters
in an item number which would then use Vlookup to fill in all of the
pertinent information such as description, price, unit of measurement,
etc....it all works great until I try to sum it all up at the end to get a
total dollar amount and the notorious #N/A screws up my SUM even if there is
only one #N/A left unchanged.

Is there a way for the SUM to ignore the #N/A? Maybe an IF.THEN statement
in the row subtotal? Any suggestions?

Lookup and #N/A
 

try the following:
=if(iserror(vlookup(...))," ",vlookup(...))

this will replace missing values where you are currently getting #N/A and
your sum will work

"Russell Johnson" wrote:

I am trying to create an inventory list that is blank unless someone enters
in an item number which would then use Vlookup to fill in all of the
pertinent information such as description, price, unit of measurement,
etc....it all works great until I try to sum it all up at the end to get a
total dollar amount and the notorious #N/A screws up my SUM even if there is
only one #N/A left unchanged.

Is there a way for the SUM to ignore the #N/A? Maybe an IF.THEN statement
in the row subtotal? Any suggestions?

Lookup and #N/A
 

But will also trap errors other than #NA which may suppress errors you need
to know about. Usually safer to specifically trap for just #NA using

=IF(ISNA(YOUR_FORMULA),0,YOUR_FORMULA)

Regards
Ken.....................


"kiersten" wrote in message
...
try the following:
=if(iserror(vlookup(...))," ",vlookup(...))

this will replace missing values where you are currently getting #N/A and
your sum will work

"Russell Johnson" wrote:

I am trying to create an inventory list that is blank unless someone
enters
in an item number which would then use Vlookup to fill in all of the
pertinent information such as description, price, unit of measurement,
etc....it all works great until I try to sum it all up at the end to get
a
total dollar amount and the notorious #N/A screws up my SUM even if there
is
only one #N/A left unchanged.

Is there a way for the SUM to ignore the #N/A? Maybe an IF.THEN
statement
in the row subtotal? Any suggestions?

Lookup and #N/A
 

=SUMIF(A:A,"<1E100")


"Russell Johnson" wrote:

I am trying to create an inventory list that is blank unless someone enters
in an item number which would then use Vlookup to fill in all of the
pertinent information such as description, price, unit of measurement,
etc....it all works great until I try to sum it all up at the end to get a
total dollar amount and the notorious #N/A screws up my SUM even if there is
only one #N/A left unchanged.

Is there a way for the SUM to ignore the #N/A? Maybe an IF.THEN statement
in the row subtotal? Any suggestions?