Impossible to sum numbers in Excel

[philip369]

I hope someone can help with this because I am pulling my hair out. 

When I try to sum numbers in a column, at the bottom of the page it gives me a count and not a sum. 

When I try to Autosum, it gives me =SUM(). 

When I try to add the specific cells, e.g.  =SUM(E2:E15) it gives me zero. 

I've read through multiple forums which suggest to make sure the cells are formatted as numbers- which they are. 

Could someone help me out here because I am completely at a loss. Thanks. 

[#2]

@philip369 

Most likely, your "numbers" are actually TEXT. Make sure that they are numbers.

[#3]

@Riny_van_Eekelen Yes... like I said I have selected the cells and marked them as numbers... but that doesn't change anything.  Grrr.

[#4]

@philip369 

Understand! But if you format a text as a NUMBER it will still be a text and the SUM function will not work. Say you have "  10" in A1. This a is a text. Try entering the formula =VALUE(A1) to make the text that looks like a number into a real number. Otherwise, upload you workbook so that I can have a look at it.

[#5]

@Riny_van_Eekelen 

Here's the sheet.. It happens with all of the sheets though.... 

[#6]

@philip369 

If your numbers are formatted as Text, changing the formula of existing numbers as General or Number won't change them back to real numbers.

To easily convert numbers entered as Text back to the real numbers, select the numbers and follow these steps...

1) Go to Data Tab.

2) Click on Text to Columns and click on Finish in the next window which pops up which is Text to Columns wizard window.

As a result, your numbers as Text will be converted into the real numbers and your existing Sum formula will return the correct output.

[#7]

@philip369 

I performed the steps I mentioned in my previous post in your sample file.

Please watch the attached video.

[#8]

@philip369 

Went into your sheet and when I select each cell in column C and press enter in the formula bar, the left justified text becomes a number. Also added a formula in column D to demonstrate what I wrote in my previous message. 

[#9]

@Subodh_Tiwari_sktneer Thank you!

[#10]

@Riny_van_Eekelen 

Thank you! Is there any way to make this a default? 
I've had many versions of Excel and this is the first time I have to go through all these steps for it to recognize text as a number (!)... Isn't that the point of Excel? 🙂 

[#11]

@philip369 

If you enter a valid number in any cell, the excel will recognize it as a number.

Normally it happens when you copy the data from external source and paste it into excel.

[#12]

@philip369 

Depends where your "numbers" come from. Importing them could cause them to be text. Just entering them them from scratch should, by default, recognise them as numbers.

[#13]

@Riny_van_Eekelen these were all manually entered... which is so strange... is there something wrong in my settings? I should have to tell it every time that it's a number, right? 

[#14]

@philip369 

No, you shouldn't have to tell Excel that you are entering numbers. That's what it expects. Sorry.

[#15]

@Riny_van_Eekelen So do you think I have some setting wrong? It's very odd... I've been using Excel for 20+ years and never had this issue.

[#16]

I have tried this many times, but in my case nothing happens with the format. it stays as it is. The data comes from a table copied from Microsoft OneNote and the column hold only number values.(no decimals, comma's, spaces or other characters). It just won't do a thing!

[#17]

Although the 'convert to table' still does not work for me, I found a way without having to F2 every cell as @Ringy_van_Eekelen mentioned.
As I said there were no spaces in the cells as I had already done CTRL+SPACE + CTRL+H type 'space' in search field and hit replace all on the specific column, but no spaces were found.
It turned out that what looked like a space in the cell behind the number, was probably something else. So I selected that white space from one cell behind the number, CTRL+C and CTRL+V in the CTRL+H search field and then hit replace all. Then it found 1001 replacements.
Then I had to copy the whole column to Notepad, create a new column in Excel, set the format to Currency and paste the data from Notepad back in the new column. Now Excel see the data as numbers(currency) and can make calculation on it.

[#18]

@philip369 

after doing a text to columns selecting the general format

[#19]

That worked for me, thank you!

[#20]

Yessss, thank you bro, it worked fantastically !!!