B
Biff
Is there a way that I can add a column that includes #DIV/0!
Biff
Biff
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
1. Insert a column
2. Select a cell in the new column
3. enter:
=0/0
2. Select a cell in the new column
3. enter:
=0/0
M
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
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
P
Peo Sjoblom
=SUMIF(A1:A500,"<="&99^99)
--
Regards,
Peo Sjoblom
--
Regards,
Peo Sjoblom
B
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.
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.
B
barry houdini
For column A try
=SUMIF(A:A,"<>#DIV/0!")
=SUMIF(A:A,"<>#DIV/0!")
D
Dave Peterson
=SUMIF(A:A,"<10E37")
10E37 is a huge number in scientific notation.
10E37 is a huge number in scientific notation.
S
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.
G
Gary''s Student
You are correct!
It appears that by "add a column" Biff meant "sum a column" rather than
"insert a column"
It appears that by "add a column" Biff meant "sum a column" rather than
"insert a column"
G
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.
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.
S
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.
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.
R
Rick Rothstein
Just a minor correction (your forgot the base 10 for the exponent)...
9E9 = 9*10^9 = 9,000,000,000
9E9 = 9*10^9 = 9,000,000,000
B
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"}))
=SUMIF(A:A,">0")
If there might be negative numbers
=SUM(SUMIF(A:A,{">0","<0"}))
A
Ashish Mathur
Hi,
You can also use
=sumif(range,"<0")+sumif(range,">0")
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
You can also use
=sumif(range,"<0")+sumif(range,">0")
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.