Impossible to sum numbers in Excel

Copper Contributor

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. 

34 Replies

@Riny_van_Eekelen or anyone else I have the same problem, and I cant get it fixed, can I maybe give my excel sheet to you so you can check? I got the excel sheet from another program (YOCO) and have tried all the suggestions..

Regards

Lena

@Lena1987 You can send it to me in a direct message or share it via OneDrive, Dropbox or something similar.

 

Bless you for your response. This has driven me crazy for a very long time. In the old days spreadsheets would let you add numbers regardless if it was text or a number (because that is the whole purpose of a spreadsheet :-D). Thank you so much for elucidating me on how to fix this ridiculous problem.

Thanks! You're welcome @davidelkinsrice! Glad you found it helpful.

@Riny_van_Eekelen 

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.

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

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?

 

 

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

@Riny_van_EekelenI had a similar issue with some financial data imported from a bank site. After noodling around, I realized that the bank website was using an em-dash for a minus sign! A quick S&R replacing the copied dash with a normal minus-sign and everything worked!

(I'll be in trouch with this bank to ask why the hell they're using em-dashes for minus-signs, too!)

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

@KaitKilpatrick 

As variant

You may copy any empty cell (Ctrl+C)

Select column with numbers as texts

Paste->Paste special->Add->Ok

 

 

I understand conceptually the difference between text and numbers but it is so incredibly frustrating that EVERY time I open a new document and manually enter numbers (not cutting and pasting) I cannot get Excel to sum.

Here's a silly example from today- new sheet, manually entered. This time I switched to commas to test whether it was an issue of being in France with a US version of Office 365 (how to switch this back to US/US format?).

Now it "sums" on the bottom right and gives me an average but instead of a summed number it gives me

I understand conceptually the difference between text and numbers but it is so incredibly frustrating that EVERY time I open a new document and manually enter numbers (not cutting and pasting) I cannot get Excel to sum.

Here's a silly example from today- new sheet, manually entered. This time I switched to commas to test whether it was an issue of being in France with a US version of Office 365 (how to switch this back to US/US format?).

Now it "sums" on the bottom right and gives me an average but instead of a summed number it gives me =SUM(A1:A5) AAARGH!! 

What do I need to change here? Microsoft makes this so incredibly difficult!

Thanks.

AAARGH!! What do I need to change here? Microsoft makes this so incredibly difficult!

Thanks.

@philip369 

Activate the Formulas tab of the ribbon and look at the Formula Auditing group.

You'll notice that the 'Show Formulas' button is highlighted.

HansVogelaar_0-1682598172512.png

Click this button to turn it off. You should now see the result of the formula.

 

Thanks! That worked!!!
Now why is that the default?

@philip369 

'Show Formulas' is off by default. Perhaps you turned it on by accident - the keyboard shortcut is Ctrl+`