Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Cells Returning #N/A
I am setting up a sheet that when a code is entered elsewhere in the workbook
it fills the information in on a summary sheet. For various reasons, we have a set number of lines (say 5) but if we only enter 3 codes the other two show up as #n/a because there is no code entered although the formula is there. On the summary sheet we need to do a simple =sum() function but if one of the cells contains #n/a then it won't calculate it. Is there a way to get the sum function to ignore any cells which come up as #n/a but to include them when it has a proper value? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Cells Returning #N/A
Hi
Wrap your formulae in an IF function. =IF(input_cell="","",your_formula) Change input-Cell to the cell reference where you input your code, and substitute your actual formula for your_formula -- Regards Roger Govier "Vasquoy" wrote in message ... I am setting up a sheet that when a code is entered elsewhere in the workbook it fills the information in on a summary sheet. For various reasons, we have a set number of lines (say 5) but if we only enter 3 codes the other two show up as #n/a because there is no code entered although the formula is there. On the summary sheet we need to do a simple =sum() function but if one of the cells contains #n/a then it won't calculate it. Is there a way to get the sum function to ignore any cells which come up as #n/a but to include them when it has a proper value? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Cells Returning #N/A
=IF(ISNA(your lookup),"",your lookup)
- Jon ------- Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Vasquoy" wrote in message ... I am setting up a sheet that when a code is entered elsewhere in the workbook it fills the information in on a summary sheet. For various reasons, we have a set number of lines (say 5) but if we only enter 3 codes the other two show up as #n/a because there is no code entered although the formula is there. On the summary sheet we need to do a simple =sum() function but if one of the cells contains #n/a then it won't calculate it. Is there a way to get the sum function to ignore any cells which come up as #n/a but to include them when it has a proper value? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Cells Returning #N/A
You could use a formula like:
=SUMIF(A:A,"<1E37") 1E37 is a very large number--1 followed by 37 0's. Or if the only errors in the =na() error: =SUMIF(A:A,"<#N/A") You could write the formulas like this, too: =SUMIF(A:A,"<"&1E+37) =SUMIF(A:A,"<"&"#N/A") It may make it easier to understand. Vasquoy wrote: I am setting up a sheet that when a code is entered elsewhere in the workbook it fills the information in on a summary sheet. For various reasons, we have a set number of lines (say 5) but if we only enter 3 codes the other two show up as #n/a because there is no code entered although the formula is there. On the summary sheet we need to do a simple =sum() function but if one of the cells contains #n/a then it won't calculate it. Is there a way to get the sum function to ignore any cells which come up as #n/a but to include them when it has a proper value? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Cells Returning #N/A
Hi,
I do a lot of formulas in Excel and as Jn specified, you should use one of the built-in functions to accomplish your task. Use IsNA() or in general for any errors, use IsError() functions, which handles any type of errors like #VALUE, #REF, etc... "Vasquoy" wrote: I am setting up a sheet that when a code is entered elsewhere in the workbook it fills the information in on a summary sheet. For various reasons, we have a set number of lines (say 5) but if we only enter 3 codes the other two show up as #n/a because there is no code entered although the formula is there. On the summary sheet we need to do a simple =sum() function but if one of the cells contains #n/a then it won't calculate it. Is there a way to get the sum function to ignore any cells which come up as #n/a but to include them when it has a proper value? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignoring empty cells | Excel Worksheet Functions | |||
Sum ignoring cells with error | Excel Worksheet Functions | |||
Ignoring Blank Cells | Excel Worksheet Functions | |||
Ignoring highlighted cells | Excel Worksheet Functions | |||
Ignoring empty cells | Excel Discussion (Misc queries) |