Dec 05 2019 07:57 AM
Dec 05 2019 07:57 AM
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.
Dec 05 2019 08:53 AM - edited Dec 05 2019 08:55 AM
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.
Dec 05 2019 09:03 AM
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.
Dec 05 2019 09:14 AM
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?
Dec 22 2021 12:13 AM
Dec 22 2021 01:03 AM - edited Dec 22 2021 01:04 AM
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.