Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
I am trying to add a column that has #NA as a result of using the Vlookup
function. I would like to add up the column that does not have #NA as an answer. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
Change the cells with the vlookup formula in to this:
=IF(ISNA(your_formula),"",your_formula) and copy down. The sum of this column should now work, but you could change the "" in the middle to 0 (zero) if you prefer. Hope this helps. Pete On Aug 29, 4:36 pm, sherman wrote: I am trying to add a column that has #NA as a result of using the Vlookup function. I would like to add up the column that does not have #NA as an answer. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
Hi Shreman,
Change your vlookup as- =IF(ISERROR(<vlookup),0,<vlookup) this will replace all the #N/A with 0 and your sum will work. -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "sherman" wrote: I am trying to add a column that has #NA as a result of using the Vlookup function. I would like to add up the column that does not have #NA as an answer. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
Best if you remove the #NA:
=if(ISNA(Vlookup),"",Vlookup) or =if(ISNA(Vlookup),0,Vlookup) "sherman" wrote: I am trying to add a column that has #NA as a result of using the Vlookup function. I would like to add up the column that does not have #NA as an answer. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
You want to avoid the NA's in the first place. Change your vlookups to
something like this... if(countif(range, item) = 0, 0, vlookup(item, range, column, false)) or if(isna(vlookup), 0, vlookup) The first option is a bit more efficient but it is a bit more work to write... -- HTH... Jim Thomlinson "sherman" wrote: I am trying to add a column that has #NA as a result of using the Vlookup function. I would like to add up the column that does not have #NA as an answer. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
=SUMIF(B2:B100,"<="&99^99)
will disregard any #N/A errors in B2:B100 although you might want to fix it in the vlookup formulas instead by using IF(ISNA(Vlookup),"",Vlookup) -- Regards, Peo Sjoblom "sherman" wrote in message ... I am trying to add a column that has #NA as a result of using the Vlookup function. I would like to add up the column that does not have #NA as an answer. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
=SUMIF(A1:A6,"<#N/A")
-- Gary''s Student - gsnu200740 "sherman" wrote: I am trying to add a column that has #NA as a result of using the Vlookup function. I would like to add up the column that does not have #NA as an answer. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
As an array formula (press Ctrl+Shift+Enter to confirm):
=SUM(IF(ISERROR(A1:A99),0,A1:A99)) Using error-checking in the lookup formula is a better approach, but the above will work. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I sum a column that has #NA as an answer to a lookup?
I like your equation. It is more general than just testing for #N/A.
-- Gary''s Student - gsnu200740 "iliace" wrote: As an array formula (press Ctrl+Shift+Enter to confirm): =SUM(IF(ISERROR(A1:A99),0,A1:A99)) Using error-checking in the lookup formula is a better approach, but the above will work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I show complete answer column | Excel Discussion (Misc queries) | |||
column number of the answer to MIN | Excel Worksheet Functions | |||
Searching a column and returning a yes or no answer | Excel Worksheet Functions | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
using lookup to return more than one answer in date format | Excel Worksheet Functions |