Sum Function w/ #DIV/0!

  • Thread starter Thread starter Biff
  • Start date Start date
Try

SUM(IF(NOT(ISERROR(A1:A10)),A1:A10))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike
 
I'm not sure I understand and maybe I didn't use the correct words. I have
column I need to summarize using the sum function, but it has cells with
formulas that have derived the #DIV/0! error message. Is there a way that I
can still sum the column because write now I am getting the same #DIV/0! in
my results for the sum function.
 
You are correct!

It appears that by "add a column" Biff meant "sum a column" rather than
"insert a column"
 
O.K. then:

Say we want to sum E1 thru E20 and remove ALL errors:

=SUM(IF(ISNUMBER(E1:E20),E1:E20,""))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
 
Hi,

And since 9E9 > 9^9 and = 9,000,000,000 which is probably bigger than any
single thing you have to sum

=SUMIF(A:A,"<9E9")

Which is really just a shorter version of a previous example. You could
also range name the range A and enter <1E304" in a cell and name it E and
then your formula

=SUMIF(A,E)

Which is not infomative by is short.
 
Just a minor correction (your forgot the base 10 for the exponent)...

9E9 = 9*10^9 = 9,000,000,000
 
If all numbers in the range are positive it's sufficient to use

=SUMIF(A:A,">0")

If there might be negative numbers

=SUM(SUMIF(A:A,{">0","<0"}))
 
Back
Top