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
hi
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
yes
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
yep
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
yep
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
thx
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.
