Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |