auto sum not working

[juster21]

i have 10 cells that i am trying to use auto sum on and the result is 0. it should be something besides 0....any ideas what is going on?

 

[Joe4]

Please try the ISNUMBER formula I talked about in the previous post, and let me know what you get for a result.

If that returns TRUE, can you post the formula you get when you use AutoSum and confirm that it is the correct range?

 

[juster21]

=ISNUMBER(E5) returns 0 but should be 82

 

[Joe4]

No, ISNUMBER does not return the number, it simply returns TRUE (1) or FALSE (0), depending on if it is a number or not.

Since it returned a 0 (which means FALSE), it means that Excel does NOT recognize your range as being numbers. Do the following to convert them:

1. Highlight your column
2. Change the Format to Number
3. With your column still highlighted, from the Data menu, select Text to Columns, and click Finish

Then try the SUM.

 

[juster21]

no good. still won't calculate.

 

[Joe4]

If you try the ISNUMBER function again, after you did those steps, does it still show a 0?

What exactly is your SUM formula?

It might be helpful if you could post a section of your data.

 

[juster21]

=ISNUMBER(E3) results in 0
the formula i'm using is =SUM(E2:E6)

 

[Joe4]

There is just one number per cell, right?

OK. Let's try this one more time, but with a few extra steps:
1. Highlight the entire column E and leave it highlighted for all steps below
2. From the Format menu, got to Cells tab and select the Number option
3. From the Data menu, select Text to Columns
4. On Step 1, make sure the Delimited option is selected, then click Next
5. On Step 2, uncheck all Delimiters so that none are selecte, then click Next
6. On Step 3, check the General radio button, and click Finish

Now, re-enter your SUM function in column E.

 

[juster21]

this is driving me crazy....it still doesn't work!! Any ideas what the problem could be?

 

[hwcarter11]

Tried duplicating your issue and found that you can multiply a number formatted as text and get a valid result and autosum will not work on the text, so that is not a valid test to say it is formatted as a number.

I have found that at times when I first enter numbers as text, that Excel doesn't always convert them using the basic command, and vice-versa trying to convert to text.

Since you only have 10 items, go to formula bar and edit the formula, just go into edit mode - you don't have to change anything, hten hit enter. This will likely change the format of the number from text to numeric.

I am just a lurker waiting for an answer to my question, not an expert. I don't know why this happens or what an easier fix is but I have found it to be an issue several times working in Excel. Hope it helps.

 

[hwcarter11]

Clarifying my earlier post...highlight the cells where you have your data and format them as numeric. (format,cells,number) If you re-enter the numeric values in the cells, I am betting the autosum will calculate.

You can either re-enter the values or go to the cell and click the formula bar to enter edit mode and then enter and it will change.

I don't know why this happens or why the data isn't changed to numeric using the format,cells,number command.