Sum Function w/ #DIV/0!

[Biff]

Is there a way that I can add a column that includes #DIV/0!

Biff

 

[Gary''s Student]

1. Insert a column
2. Select a cell in the new column
3. enter:
=0/0

 

[Mike H]

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

 

[Peo Sjoblom]

=SUMIF(A1:A500,"<="&99^99)

--


Regards,


Peo Sjoblom

 

[Biff]

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.

 

[barry houdini]

For column A try

=SUMIF(A:A,"<>#DIV/0!")

 

[Dave Peterson]

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

10E37 is a huge number in scientific notation.

 

[Spiky]

1. Insert a column
2. Select a cell in the new column
3. enter:
    =0/0

LOL! I think he already figured that part out.

 

[Gary''s Student]

You are correct!

It appears that by "add a column" Biff meant "sum a column" rather than
"insert a column"

 

[Gary''s Student]

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.

 

[Shane Devenshire]

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.

 

[Rick Rothstein]

Just a minor correction (your forgot the base 10 for the exponent)...

9E9 = 9*10^9 = 9,000,000,000

 

[barry houdini]

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"}))

 

[Ashish Mathur]

Hi,

You can also use

=sumif(range,"<0")+sumif(range,">0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com