Sum Function w/ #DIV/0!
 

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

Biff

Sum Function w/ #DIV/0!
 

1. Insert a column
2. Select a cell in the new column
3. enter:
=0/0
--
Gary''s Student - gsnu200822


"Biff" wrote:

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

Biff

Sum Function w/ #DIV/0!
 

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

"Biff" wrote:

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

Biff

Sum Function w/ #DIV/0!
 

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

--


Regards,


Peo Sjoblom

"Biff" wrote in message
...
Is there a way that I can add a column that includes #DIV/0!

Biff

Sum Function w/ #DIV/0!
 

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.

"Gary''s Student" wrote:

1. Insert a column
2. Select a cell in the new column
3. enter:
=0/0
--
Gary''s Student - gsnu200822


"Biff" wrote:

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

Biff

Sum Function w/ #DIV/0!
 

For column A try

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

Sum Function w/ #DIV/0!
 

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

10E37 is a huge number in scientific notation.

Biff wrote:

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

Biff

--

Dave Peterson

Sum Function w/ #DIV/0!
 

On Dec 30, 11:45*am, Gary''s Student
wrote:
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.

Sum Function w/ #DIV/0!
 

You are correct!

It appears that by "add a column" Biff meant "sum a column" rather than
"insert a column"
--
Gary''s Student - gsnu200822


"Spiky" wrote:

On Dec 30, 11:45 am, Gary''s Student
wrote:
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.

Sum Function w/ #DIV/0!
 

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.
--
Gary''s Student - gsnu200822


"Biff" wrote:

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.

"Gary''s Student" wrote:

1. Insert a column
2. Select a cell in the new column
3. enter:
=0/0
--
Gary''s Student - gsnu200822


"Biff" wrote:

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

Biff

Sum Function w/ #DIV/0!
 

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.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Biff" wrote:

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

Biff

Sum Function w/ #DIV/0!
 

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

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

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
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.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Biff" wrote:

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

Biff

Sum Function w/ #DIV/0!
 

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

Sum Function w/ #DIV/0!
 

Hi,

You can also use

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Biff" wrote in message
...
Is there a way that I can add a column that includes #DIV/0!

Biff