Is there an easy way to sum a column but have it ignore #NA and #Div/0!

The file I am using is fairly large and I'd like avoid build IF formulas to
replace the errors

Thanks

One way:

=SUMIF(A:A,"<10E37")

10E37 is a huge number in scientific notation.

Another way is to use an array formula:

=sum(if(isnumber(a1:a999),a1:a999))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



Darby wrote:

Is there an easy way to sum a column but have it ignore #NA and #Div/0!

The file I am using is fairly large and I'd like avoid build IF formulas to
replace the errors

Thanks

--

Dave Peterson

perfect, thanks

"Dave Peterson" wrote:

One way:

=SUMIF(A:A,"<10E37")

10E37 is a huge number in scientific notation.

Another way is to use an array formula:

=sum(if(isnumber(a1:a999),a1:a999))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.



Darby wrote:

Is there an easy way to sum a column but have it ignore #NA and #Div/0!

The file I am using is fairly large and I'd like avoid build IF formulas to
replace the errors

Thanks

--

Dave Peterson