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]

@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

[#3]

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

[#4]

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.

[#5]

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

[#6]

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

[#7]

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

[#8]

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?

[#9]

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

[#10]

@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!)

[#11]

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?

[#12]

@KaitKilpatrick 

As variant

You may copy any empty cell (Ctrl+C)

Select column with numbers as texts

Paste->Paste special->Add->Ok

[#13]

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.

[#14]

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

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

[#15]

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

[#16]

@philip369 

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

[#17]

@philip369  I had the same problem when cutting and pasting from a web page. Numbers were text. All I did was a Replace of space to @ then replace the @ with nothing. Trying to replace space with nothing did exactly that - nothing - it didn't remove the spaces. 

Text to columns did nothing, I have yet to come across anything where that function works to be honest and I've been using Excel since it came out (I'm an old programmer!).

[#18]

@DrDave990 

copy/pasting from web usually adds non breaking space (char code 160), which is not printable character. You may check with =LEN(A1) and compare with number of visible characters.

On 365 or 2021 you may check exact codes with =CODE( MID( A1, SEQUENCE(LEN(A1), 1) ).

Or rightmost code as =CODE( RIGHT(A1) ). In general it could be other non-printable characters. If they are you may remove them

=1*SUBSTITUTE(A1, CHAR(160), "" )

with 1* to convert result to number.

[#19]

That's because a hidden character somehow snuck in the numbers and prevents you from using those as numbers. CHAR(0160) didn't work for me.

Only 100% foolproof way that I found was to
1) save the excel file as a *.txt
2) open the txt file, select the whole file (CTRL + A), the hidden text characters will now appear as ?
3) Find replace ? with nothing.
4) Copy paste the whole thing back to excel.

[#20]

This is a discussion that seems to go on forever!

Another option, that has come about recently, is to forget about characters you do not want and focus on extracting those you require.  For a single cell you might have

= VALUE(CONCAT(REGEXEXTRACT(@import, "(\d|\.|-)+", 1)))

but for an entire array of imported data,  this becomes,

= MAP(
     import,
     LAMBDA(data,
        VALUE(CONCAT(REGEXEXTRACT(data, "(\d|\.|-)+", 1)))
     )
  )

Life gets more complicated if you need to pick out dates, numbers in scientific notation etc.

[#21]

@philip369 

I realise that people keep arguing about this issue so here's some test data that we received from a Powerpoint table.

NB​

73​

49​

29​

68​

104​

52​

122​

22​

66​

73​

56​

Here's the ultimate solution.

1) Your numbers are likely saved as text and excel needs to be reminded to process them as numbers.

* If it is a single cell and there's a green triangle in the upper corner, try clicking on it and convert it manually.

* If it is a column of numbers, your first instinct should be to use the "Text to Columns" tool to change the data type into standard. Select the column > Data Tab > Text to Columns > Finish.

* If it's a whole table, use the "Text to Columns" tool, column by column.

 Select the column > Data Tab > Text to Columns > Finish.

2) If the above doesn't work, your numbers probably have a hidden character somewhere which forces excel to consider them as text. To know if there are any hidden characters in cell A1

* Click on B1 > Type formula =LEN(A1) and if there is a difference between the characters you see and the number of characters written in B1 then you'll know that there's some shenanigans somewhere. The likely culprit is CHAR(160) which is both invisible and unselectable but it's not always the case. 

You can use =TRIM(CLEAN(A1)) to try and find the hidden characters or use VBA/Powerquery but not all of them are destroyed and some hidden characters might escape detection

If all else fails, copy the offending data into another excel sheet and save as Tab Delimited Text (. txt)

It will save all data into UTF-8 and show the hidden characters that are causing the issue. Be warned as it replace all formulas into their corresponding result. 

Using the above example, you should have this as a result:

NB?
73?

You can then proceed to replace the "?" by "" using CTRL + H, then paste back the values into your excel sheet.

3) Someone else has mentioned using a Python Script to clean the table but not everyone has access to it and I am pretty sure that it will depend on a case-to-case basis. 

In any case, I hope it helps.
If someone has a better solution, I would be very interested to hear about it. I have enclosed the offending columns in my answer.