Adding numbers in a column results in zero: why?

A

Allewyn

When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
 
D

David Billigmeier

For your first problem: It is probably because these cell are formated as
text, the sum() function doesn't include these. To fix embed the VALUE()
function, like so: =SUM(VALUE(A2:A8)), and confirm with CTRL+SHIFT+ENTER as
it is an array function.

For your second problem, it is probably the same reason, the cell is
formatted as text before you enter the formula. To fix, change the
formatting to "General" and re-enter the formula.

Does that help?
 
A

Allewyn

Ok, the text doesn't show in the cell anymore but the result is still when it
should be 12. Thanks for solving part of it :)

David Billigmeier said:
For your first problem: It is probably because these cell are formated as
text, the sum() function doesn't include these. To fix embed the VALUE()
function, like so: =SUM(VALUE(A2:A8)), and confirm with CTRL+SHIFT+ENTER as
it is an array function.

For your second problem, it is probably the same reason, the cell is
formatted as text before you enter the formula. To fix, change the
formatting to "General" and re-enter the formula.

Does that help?
--
Regards,
Dave


Allewyn said:
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
 
A

Allewyn

Some of the cells I dragged highlighting over to get the range of values in
conditionally formatted. I forgot to mention that. Will that casue the
problem?

David Billigmeier said:
For your first problem: It is probably because these cell are formated as
text, the sum() function doesn't include these. To fix embed the VALUE()
function, like so: =SUM(VALUE(A2:A8)), and confirm with CTRL+SHIFT+ENTER as
it is an array function.

For your second problem, it is probably the same reason, the cell is
formatted as text before you enter the formula. To fix, change the
formatting to "General" and re-enter the formula.

Does that help?
--
Regards,
Dave


Allewyn said:
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
 
A

Allewyn

Embedding VALUE as in "=sum(VALUE(A2:A8)) results in the display #VALUE in
the cell

:

Does that help?
--
Regards,
Dave


Allewyn said:
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
 
D

David Billigmeier

Did you confirm the formula with CTRL+SHIFT+ENTER?

--
Regards,
Dave


Allewyn said:
Embedding VALUE as in "=sum(VALUE(A2:A8)) results in the display #VALUE in
the cell

:

Does that help?
--
Regards,
Dave


Allewyn said:
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
Click to expand...
 
A

Allewyn

David - Innotcied that the proper range was being selected automatically
while I was typing it. When I pushed SHIFT CONTROL ENTER the formula in the
upper bar showed with {brackets} and the numbers added when I pressed ENTER.
Am I going to have to do that all the time?
David Billigmeier said:
Did you confirm the formula with CTRL+SHIFT+ENTER?

--
Regards,
Dave


Allewyn said:
Embedding VALUE as in "=sum(VALUE(A2:A8)) results in the display #VALUE in
the cell

:

Does that help?
--
Regards,
Dave


:

When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

Allewyn

Try this method.

Format the cells as General.

Copy a blank cell and select the others then Paste Special>Add>OK>Esc.


Gord Dibben Excel MVP

Embedding VALUE as in "=sum(VALUE(A2:A8)) results in the display #VALUE in
the cell

:

Does that help?
--
Regards,
Dave


Allewyn said:
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
Click to expand...
 
D

David Billigmeier

The curly brackets {} show up because it is array entered (which is what
committing a formula with CTRL+SHIFT+ENTER does).

Yes, If you use the formula with the VALUE() embedded, you will have to hit
CTRL+SHIFT+ENTER every time.

Another option, if you don't want to array enter this formula is to enter 1
in a blank cell, then copy this cell, select the range of numbers you are
trying to add with the sum() funtion (in your example A2:A8), right click,
select "Paste Special", check "Multiply", and click OK. This will convert
the text in these cells to numbers. In this case you can just use the SUM()
function without embedding VALUE(). i.e. =SUM(A2:A8) will work this time.




--
Regards,
Dave


Allewyn said:
David - Innotcied that the proper range was being selected automatically
while I was typing it. When I pushed SHIFT CONTROL ENTER the formula in the
upper bar showed with {brackets} and the numbers added when I pressed ENTER.
Am I going to have to do that all the time?
David Billigmeier said:
Did you confirm the formula with CTRL+SHIFT+ENTER?

--
Regards,
Dave


Allewyn said:
Embedding VALUE as in "=sum(VALUE(A2:A8)) results in the display #VALUE in
the cell

:

<snip>


Does that help?
--
Regards,
Dave


:

When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
Click to expand...
 
A

Allewyn

Thanks everyone! Problem solved.

Gord Dibben said:
Allewyn

Try this method.

Format the cells as General.

Copy a blank cell and select the others then Paste Special>Add>OK>Esc.


Gord Dibben Excel MVP

Embedding VALUE as in "=sum(VALUE(A2:A8)) results in the display #VALUE in
the cell

:

Does that help?
--
Regards,
Dave


:

When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.

Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell. Try THAT one!
Click to expand...
Click to expand...
Click to expand...
 
Top