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?

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?

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?

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

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?