Excel - SUM Function Problem - Expert Solution

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

auto sum is not showing correct number, it doesn't with dragging across numbers either.
Solved by T. U. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 20/04/2018 - 10:57
Hello there, thanks for choosing Got It Pro - Excel.
User 20/04/2018 - 10:57
Excelchat Expert 20/04/2018 - 10:58
So you want to auto sum a column or row ?
User 20/04/2018 - 10:59
yes, but it is not working. I have formulas in the column
User 20/04/2018 - 10:59
not sure why it is not adding up. tried to paste values and still not working
Excelchat Expert 20/04/2018 - 11:00
Okay than you are using auto sum function in B column from B1 to B14 ?
User 20/04/2018 - 11:00
made sure formate as numbers too
User 20/04/2018 - 11:00
yes, understand that
Excelchat Expert 20/04/2018 - 11:01
You have blank cells on your column.
User 20/04/2018 - 11:01
in my sheet, it comes up 2.28 but it should be the 7.56
User 20/04/2018 - 11:01
User 20/04/2018 - 11:01
shows no value even when i click and drag
User 20/04/2018 - 11:02
looks like it is only calculating these numbers.
Excelchat Expert 20/04/2018 - 11:02
A formula such as =SUM(B1:B14) ,blanks will count as zero.
User 20/04/2018 - 11:03
Excelchat Expert 20/04/2018 - 11:04
and the result is 7.56
Excelchat Expert 20/04/2018 - 11:05
Auto sum have problems with blank cells and also with Circular dependency.
User 20/04/2018 - 11:06
okay, I think I have gotten closer to the problem.
User 20/04/2018 - 11:07
I'm pasted a sheet into my sheet and the data is not being seen as a number. if I type in a number to replace it, the autosum works
Excelchat Expert 20/04/2018 - 11:08
If you try in Google sheets then is working well, at first you select the range in your case B1:B14, then Insert ---> Function ----> Sum
User 20/04/2018 - 11:09
works when I pasted in here. but not when I pasted it into excel
User 20/04/2018 - 11:09
from other sheet.
Excelchat Expert 20/04/2018 - 11:10
Okay then you have to change the format as a number and check if it works.
User 20/04/2018 - 11:10
did that. it doesn't work
Excelchat Expert 20/04/2018 - 11:11
Try to write this formula : =SUM(B1:B14)
Excelchat Expert 20/04/2018 - 11:11
And you have N/a as a text or is a result from formula ?
Excelchat Expert 20/04/2018 - 11:14
Can you please attach your excel document here ?
User 20/04/2018 - 11:14
for some reason, the numbers are seen as numbers when I paste them. even I change their format to numbers
User 20/04/2018 - 11:15
can't. proprietary info on it.
User 20/04/2018 - 11:15
take me a while to remove all of it to make it generic
Excelchat Expert 20/04/2018 - 11:15
Okay, no problem.
User 20/04/2018 - 11:16
have ever seen cells with numbers not be seen as numbers even after formatting them to numbers
Excelchat Expert 20/04/2018 - 11:19
No, changing format of column to numbers then it should seen as numbers after formatting them to numbers.
User 20/04/2018 - 11:20
yeah, but that's not working. weird. I have even copied them and pasted special as values with number formatting and still are not seen as numbers
Excelchat Expert 20/04/2018 - 11:21
Even this formula is not showing the right result =SUM(B1:B14) ?
User 20/04/2018 - 11:21
Excelchat Expert 20/04/2018 - 11:22
In your formula you can add ={your formula}*1
Excelchat Expert 20/04/2018 - 11:22
Then the result of formula will format as a number,
Excelchat Expert 20/04/2018 - 11:23
I mean in formulas of your cells in the range you want to sum
User 20/04/2018 - 11:25
*1 worked on all but 1
User 20/04/2018 - 11:26
ok, got it.
User 20/04/2018 - 11:26
thanks for the help
Excelchat Expert 20/04/2018 - 11:26
Thanks for choosing us :)
Excelchat Expert 20/04/2018 - 11:26
If you need our help please post again.
Excelchat Expert 20/04/2018 - 11:27
Please give your kind feedback for our serivce.
Excelchat Expert 20/04/2018 - 11:27
Bye, have a good day ahead.
User 20/04/2018 - 11:27

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc