Formula won't show sum

[Parry]

I'm a novice...and self-taught, but I love Excel. Can someone please explain
why when I enter a formula to add a column of numbers, the formula is showing
in the cell and not the sum. the formula is: =sum(e27:e42) Have they
changed how a formula is written in Excel 2007? Even after I format the
cells to be numbers versus text, the sum function won't show a sum...just the
formula. Sometimes all I get is a zero. How can I fix this? It's very
frustrating. Thx.
--
Novice
Thanks

[Dave]

Hi,
In earlier versions, Tools menu, Options.
On the View tab, deselect Formulas.
Not sure how this differs for 2007
Regards - Dave.

[Parry]

Hi Dave...this worked for a couple of other formulas, but not for the ones in
question. I'm running Excel 2007. I'm stumped. Can you help?
--
Novice
Thanks

[Ken Johnson]

Reformatting from Text to Number doesn't fix the problem of the cells
being treated as Text. Enter a 1 into a free cell that is definitely
General format then copy it then select E27:E42 and Paste Special
using the Multiply operation. The Sum should then be OK if that was
the problem.

Ken Johnson

[Rick Rothstein (MVP - VB)]

It sounds like the cell was formatted as Text when the formula was entered.
Try this... delete the entry in the cell, then change its format to General,
and then type the formula over again.

Rick

"Parry" <Pa...@discussions.microsoft.com> wrote in message
news:FD5F36A6-262A-4776...@microsoft.com...

[RagDyer]

Try this:

Select the cell with the text formula, then:
<Ctrl> <Shift> < ~ >
Then
<F2>
Then <Enter>

The first is a keyboard shortcut to format the cell to General.
The second is to enter the "Edit" mode.
And the 3rd is to register the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Parry" <Pa...@discussions.microsoft.com> wrote in message
news:FD5F36A6-262A-4776...@microsoft.com...

[Parry]

Tried that...several times and it still shows the formula and not the sum.
Sorry.
--
Novice
Thanks

[Parry]

I found a general cell, put a one in it, copied it to the cell in question
that kept showing the formula...and now I have a total, but it's wrong.
Instead of 17.00 it's 0.00. I also don't know what you mean by Paste Sepcial
using the Multiply operation...so I couldn't do that. Can you be more
specific?
--
Novice
Thanks

[Parry]

Thanks, but this didn't work either. Why is this so hard? I've even tried
to use a formula in another cell to total it, and still can't get a sum.
--
Novice
Thanks

[Parry]

Even if I find a general cell, and enter the formula for the sum of that
range, I still get 0.00. The numbers in the range are formatted as
numbers...unless they were once text? Could that be the problem?
--
Novice
Thanks

[Parry]

That's it...the numbers I was trying to add, had at one time been formatted
as text. I found a one that was general, copied it into these cells, and now
they are adding. Thanks a lot...great little puzzle.
--
Novice
Thanks

[Ken Johnson]

Hi Parry,

I know you've solved the problem.
Just thought I'd try to clear up any confusion about my suggestion.
If cells (E27:E42) are formatted as Text and then have numbers entered
into them, a formula summing those cells will return 0.
If you reformat the cells to either General or Number, even though the
format has changed, Excel still treats them as Text and the sum
remains 0.
My suggestion was to get Excel to treat them as numbers by copying a 1
from a General formatted cell, then select E27:E42 then go Edit|Paste
Special... to bring up the Paste Special dialog. On that dialog there
is an area with the heading "Operation". The choices are None
(default), Add, Subtract, Multiply and Divide. If you choose Multiply
(or Divide), then pasting multiplies (or divides) each cell by 1. This
has no effect on the cells' values but from that point on Excel treats
them as numbers and the sum formula should return the expected result.

If you have a cell with a formula and instead of seeing the calculated
result you see the formula, then that can be caused by the cell being
formatted Text before the formula was entered. The above method will
not work in this case.
You can instead reformat the cell to General, select the whole formula
in the Formula Bar (or double click the cell and select it in the
cell) then copy and paste (Ctrl C then Ctrl V) then Enter.

Having said all that, I prefer RagDyer's method. It works the same way
for both formulas and values.

Ken Johnson

[Parry]

Wow, thanks for taking the time for the complete explanation. I understand now.

[justinsomething]

I have tried all of the suggestions/guidance here and in a KB article of the same subject and I still have the same prob. All I get is a formula in the intended destination. How do I get a simple SUM? Very frustrated. Any help you can provide is greatly appreciated

[T. Valko]

It sounds like the cell that holds the formula is formatted as TEXT. Change
the format to GENERAL then double click the cell then hit ENTER.

Another possibility is that you may have formula view activated. Navigate to
Tools>Options>View tab. Under Window options, uncheck Formulas>OK

--
Biff
Microsoft Excel MVP

<justin something> wrote in message news:2008741361...@comcast.net...

[excell]

Excel sometimes does not evaluate formulas due to errors. I had a similar problem, which I could fix by correcting some circular cell references in some of my cells.

Try the "Error Checking" option in the Furmala tab to find and fix errors.

[Angel]

Try going to 1. excel option 2. formulas right side
calculation options click on Automatic

[Marcie]

Good Afternoon! I am having a similar problem and have tried all of the
suggestions below and nothing worked. I copied some values from my cell
phone bill on the net and tried to sum them. The values copied very nicely
into Excel but do not sum. If i retype the numbers over the original number
they will then start to sum which to me suggests formatting. Keep in mind
I've tried all of the below mentioned tricks. Any more thoughts? Thanks!
Marcie

[Hayden@discussions.microsoft.com Dennis (The Menace) Hayden]

I have this problem also. I even tried moving the spreadsheet to different
PCs, and opening it in 2007.

In the end I substituted =SUM(A1:A5) for =A1+A2+A3+A4+A5 And low and behold
it works. So there seems to be "SUMthing" wrong with this function under
certain conditions.

Den

[Dennis (The Menace) Hayden]

More on this......

One possible cause is copying/pasteing/linking to a cell with embedded £, $
etc signs in them. They will still add up using A1+A2 etc but SUM cannot hack
the embedded signs.

The solution is to use the VALUE function which strips out the embedded
signs and SUM will now work. This will probably mean duplicate cells to make
it work. EG

=VALUE(enter in here the link or cell the problem number is in). Then SUM
this column/row.

This doesn't seem to have anything to do with Options or cell formats.

The Menace

[Mathew Trickel]

I have had this problem happen several times over the years.
This finally worked for me.
Check the string of data calculations since we are adding numbers, all the source data should also be formatted as numbers.
If one number in any of those cells is automatic or text It will not add. All reference cells must be numbers.

Mat




--------------------------------------------------------------