Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

The sum of NUMBERS, not text, add up to zero instead of the correct number.
Solved by D. D. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 06/07/2018 - 05:26
Hello, I understand that your sum formula is returning a zero result even though you expect something else, right?
Excelchat Expert 06/07/2018 - 05:26
Are you able to share your file so I can analyze it?
User 06/07/2018 - 05:26
I am not able to share it
User 06/07/2018 - 05:27
But yes, that is the problem
User 06/07/2018 - 05:27
The cells are numbers, not text
Excelchat Expert 06/07/2018 - 05:27
Can you create a copy of your file with just the numbers?
Excelchat Expert 06/07/2018 - 05:28
I'm asking because sometimes there are things that aren't obvious but are causing the problem.
User 06/07/2018 - 05:28
I don't think I am able to create a copy. Is there anything I can try to fix this ?
Excelchat Expert 06/07/2018 - 05:29
There are several ways to fix this. But that's what makes this difficult, there's just too many possible reasons.
Excelchat Expert 06/07/2018 - 05:29
Let's start by trying to recreate your data using the document to the right.
Excelchat Expert 06/07/2018 - 05:29
Just show me how your data looks like.
User 06/07/2018 - 05:30
In my doucment, the sum does not add up to 81
Excelchat Expert 06/07/2018 - 05:31
Alright, if that's the case there is a very good chance that your numbers aren't really numbers.
Excelchat Expert 06/07/2018 - 05:31
But they may have been stored as text.
Excelchat Expert 06/07/2018 - 05:31
Without seeing the actual file, I can only speculate.
Excelchat Expert 06/07/2018 - 05:31
Let me try something.
Excelchat Expert 06/07/2018 - 05:31
Are you using Excel?
User 06/07/2018 - 05:31
Yes
User 06/07/2018 - 05:31
I am pretty sure they are all number
Excelchat Expert 06/07/2018 - 05:31
Okay, try this formula:
Excelchat Expert 06/07/2018 - 05:32
They might look like numbers but they may not be.
Excelchat Expert 06/07/2018 - 05:32
I've encountered this personally several times.
Excelchat Expert 06/07/2018 - 05:32
=isnumber(A1)
Excelchat Expert 06/07/2018 - 05:32
Try this formula and make sure that you change A1 to reference one of your numbers.
User 06/07/2018 - 05:33
Okay
User 06/07/2018 - 05:33
It says false
Excelchat Expert 06/07/2018 - 05:33
That's what I'm referring to. It's clearly not a number.
Excelchat Expert 06/07/2018 - 05:33
Are your numbers in the right side of the cell or on the left side?
User 06/07/2018 - 05:33
right
Excelchat Expert 06/07/2018 - 05:34
Okay, please select one of your numbers then change the cell formatting to NUMBER.
Excelchat Expert 06/07/2018 - 05:34
Then try the formula again to see if it results to false still.
User 06/07/2018 - 05:35
It is still false
Excelchat Expert 06/07/2018 - 05:35
That alright, there are still several ways we can do this.
Excelchat Expert 06/07/2018 - 05:35
Let's try something else.
Excelchat Expert 06/07/2018 - 05:36
About how many rows of numbers do you have?
Excelchat Expert 06/07/2018 - 05:36
Still with me?
User 06/07/2018 - 05:36
Yes\
User 06/07/2018 - 05:36
Excelchat Expert 06/07/2018 - 05:37
Try this formula in the column next to your numbers:
Excelchat Expert 06/07/2018 - 05:37
=A1*1
Excelchat Expert 06/07/2018 - 05:37
Then drag it all the way down.
User 06/07/2018 - 05:37
I think I figured it out
User 06/07/2018 - 05:38
One of the values in the column was a formula that equals zero, but not actually a zero
Excelchat Expert 06/07/2018 - 05:38
I'm sure you did. You just needed to be reassured that your numbers are not being recognized as numbers.
Excelchat Expert 06/07/2018 - 05:39
Yes, this is just one of the examples of how numbers may look like numbers but are not being recognized as one.
User 06/07/2018 - 05:39
Okay. Thank you for your help
Excelchat Expert 06/07/2018 - 05:39
Anyway, I'm glad I was of help.
Excelchat Expert 06/07/2018 - 05:39
Would there be anything else that I can help you with regards to the original question?
User 06/07/2018 - 05:39
No, I think that is all. Thanks.
Excelchat Expert 06/07/2018 - 05:40
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 06/07/2018 - 05:40
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 06/07/2018 - 05:40
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.