SUM ignoring errors

[Darby]

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]

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]

perfect, 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.