Forum Discussion
Impossible to sum numbers in Excel
Lena1987 You can send it to me in a direct message or share it via OneDrive, Dropbox or something similar.
I am having similar issue. I tried every step you described in previous scenario, on my sheet, but still no luck. Can you help me with my sheet.
- Riny_van_EekelenNov 21, 2022Platinum Contributor
eagle1981 Similar or the same? This is an old thread and deals with a classic problem where users think that formatting a cell as a number doesn't change the content to a number if it is a text.
check the cells you are summing with ISNUMBER(cell_ref). Does it return TRUE? If not, you are dealing with texts that look like numbers.
- philip369Nov 21, 2022Copper Contributor
Funny this came up again today as I am working on another big project in Excel and where this ridiculous problem seems to happen over and over and over... Why can't Microsoft fix one of the most basic things ever? Like having a spreadsheet that can sum numbers? A crazy thought, I know 🙂
Riny_van_EekelenI'm going to have to go back and review these posts again because the problem persists even with new documents I create.
But I don't understand the meaning of this sentence-->
where users think that formatting a cell as a number doesn't change the content to a number if it is a text
Can you explain what that means?
Even when I have changed it to "number" it still doesn't work. Isn't "number" the default?
- Riny_van_EekelenNov 21, 2022Platinum Contributor
philip369 When you enter a number in the grid with the keyboard, Excel will always now it's a number. Even when you accidentally hit the space bar a few times before the numbers. Provided that the cell where you enter a number is not formatted as Text.
However, when you copy a number from, let's say a website or open a CSV file, it may happen that the numbers come across as texts. Once that has happened you can NOT just format the cell to Number to transform it into a real number. After you have formatted the cell as Number you need to re-enter the content of the cell (F2 and Enter). Only then the 'text-number' becomes a real number.
You can use tools like Text-to-columns to fix such issues once they have occurred. But it's better to prevent them from happening. For instance, when you have a CSV file, DO NOT open it directly in Excel. Instead, import it via Power Query or the Legacy 'From Text' import wizard.
It happens quite often that people want to import product numbers that have leading zeroes. And then they get upset because Excel guessed that these should be numbers and deleted the leading zeroes. So that's the opposite from your situation.
The point is that the user needs to know his/her data and not let Excel guess how to interpret numbers. Same thing happens with dates, over and over again. The user must be explicit.
- KaitKilpatrickApr 05, 2023Copper ContributorHow do you fix this in modern excel? The text to columns "wizard" isn't a thing for me... It just has options to select "tab," "semicolon," "comma,", "space,", or "custom." I have this data coming into my excel workbook from an MS form, and although it's set as "number" on the form, and the column type in my workbook, when I do the ISNUMBER(cell ref) formula, I am getting "false." Can someone please help?
- SergeiBaklanApr 05, 2023MVP
As variant
You may copy any empty cell (Ctrl+C)
Select column with numbers as texts
Paste->Paste special->Add->Ok