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
|