Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of cells when an #N/A is present
Greetings,
I have a VLOOKUP and some of the results are #N/A as there is no data for that cell. I need to sum the rows of cells, including those with #N/A (those values would then be 0).When I try to add the results those error cells do not let me add up the other cells with real values. What is the best way to do this? I am okay with adding another column if needed Thank you in advance for your advice! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of cells when an #N/A is present
Steve B,
Add error traping to your VLOOKUP formulas. Change your VLOOKUP formula to this: =IF(ISNA(VLOOKUP(lookup value,lookup table,column,match)),0,VLOOKUP(lookup value,lookup table,column,match)) OR =IF(ISNA(VLOOKUP(lookup value,lookup table,column,match)),"",VLOOKUP(lookup value,lookup table,column,match)) Now, if your VLOOKUP results in an #N/A, a 0 or "" will be put in it's place, allowing your other formulas based on this column to work. HTH, Conan "Steve B" wrote in message ... Greetings, I have a VLOOKUP and some of the results are #N/A as there is no data for that cell. I need to sum the rows of cells, including those with #N/A (those values would then be 0).When I try to add the results those error cells do not let me add up the other cells with real values. What is the best way to do this? I am okay with adding another column if needed Thank you in advance for your advice! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of cells when an #N/A is present
hi,
use if(iserror(vlookup(your formula),0,(vlookup(your formula)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Steve B" escreveu: Greetings, I have a VLOOKUP and some of the results are #N/A as there is no data for that cell. I need to sum the rows of cells, including those with #N/A (those values would then be 0).When I try to add the results those error cells do not let me add up the other cells with real values. What is the best way to do this? I am okay with adding another column if needed Thank you in advance for your advice! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum of cells when an #N/A is present
Try this:
=SUMIF(A1:A10,"<#N/A") However, the best solution is to correct the lookup formulas so they don't return errors in the first place. OTOH, you may *need* these errors for charting purposes, or, you may need to see errors when they happen. -- Biff Microsoft Excel MVP "Steve B" wrote in message ... Greetings, I have a VLOOKUP and some of the results are #N/A as there is no data for that cell. I need to sum the rows of cells, including those with #N/A (those values would then be 0).When I try to add the results those error cells do not let me add up the other cells with real values. What is the best way to do this? I am okay with adding another column if needed Thank you in advance for your advice! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no saving if certain value present | Excel Discussion (Misc queries) | |||
net present value | Excel Worksheet Functions | |||
sum cells IF no text present | Excel Discussion (Misc queries) | |||
Net Present Value | New Users to Excel | |||
Present Value | New Users to Excel |