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!

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" wrote:

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!

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" wrote:

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" wrote:

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!

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" wrote:

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" wrote:

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!

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

"David Billigmeier" wrote:

<snip


Does that help?
--
Regards,
Dave


"Allewyn" wrote:

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!

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

--
Regards,
Dave


"Allewyn" wrote:

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

"David Billigmeier" wrote:

<snip


Does that help?
--
Regards,
Dave


"Allewyn" wrote:

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!

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" wrote:

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

--
Regards,
Dave


"Allewyn" wrote:

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

"David Billigmeier" wrote:

<snip


Does that help?
--
Regards,
Dave


"Allewyn" wrote:

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!

Allewyn

Try this method.

Format the cells as General.

Copy a blank cell and select the others then Paste SpecialAddOKEsc.


Gord Dibben Excel MVP

On Mon, 19 Dec 2005 12:00:02 -0800, "Allewyn"
wrote:

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

"David Billigmeier" wrote:

<snip


Does that help?
--
Regards,
Dave


"Allewyn" wrote:

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!

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" wrote:

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" wrote:

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

--
Regards,
Dave


"Allewyn" wrote:

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

"David Billigmeier" wrote:

<snip


Does that help?
--
Regards,
Dave


"Allewyn" wrote:

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!

Thanks everyone! Problem solved.

"Gord Dibben" wrote:

Allewyn

Try this method.

Format the cells as General.

Copy a blank cell and select the others then Paste SpecialAddOKEsc.


Gord Dibben Excel MVP

On Mon, 19 Dec 2005 12:00:02 -0800, "Allewyn"
wrote:

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

"David Billigmeier" wrote:

<snip


Does that help?
--
Regards,
Dave


"Allewyn" wrote:

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!