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.

 

[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?

 

[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.

 

[Ken Johnson]

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?

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]

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

 

[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?

 

[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.

 

[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?

 

[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.

 

[Ken Johnson]

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?

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.

 

[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

 

[Ex Cell]

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

 

[Kirk]

I ran into a similar problem when I imported a csv file. I found that there
was a character (probably space(s)) in front of the numbers. I did a find
(pasted in characters from one of the cells) and replace with an empty string
(replace with) and it fixed my problem. I had tired previous suggestions to
no affect. It was/were those unprintable characters in front of my numbers
that maintained the text format, once gone everything worked.

 

[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

 

[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