Why is auto sum not working

**twofootgiant** · 07-11-2008, 09:15 AM

HI everyone

OK i have a colums of numbers as follows:
8.20
12.00
50.00
36.38
18.99
121.00
20.00
7.23
9.36
58.95
0.00
68.00
600.00
59.70
40.00
52
62
40.00
39.00
251.88
15.00
78.65
53
1724.73
50.00
50.00
350
70.38
0.00
0.00

This runs from B6 to B33...but when my formula in B34 says =SUM(B6:B33) It comes out with the completly wrong value like 3.00 or something.

Any help?

**jwhitwell** · 07-11-2008, 09:21 AM

You sure its not set to 'count' (you have 30 items, looks like 3, hehe) or something other than sum (min, max, etc)?

**BigBas** · 07-11-2008, 09:49 AM

Post a .zip sample of the workbook.

Are you sure there isn't some strange custom formatting on the SUM cell?

**daddylonglegs** · 07-11-2008, 09:59 AM

Perhaps some of your numbers are text formatted (in which case SUM function ignores them). You can't just change format to numeric, try selecting column and using Data > Text to Columns > Finish...then retry SUM formula

......alternatively just try

=SUMPRODUCT(B6:B33+0)

**twofootgiant** · 07-11-2008, 10:57 AM

thanks that way worked.. all gd now

**Kotshome** · 09-05-2013, 12:39 AM

Hi,

I recently encountered this problem and tried searching a lot frantically for a solution, I tried formating the column to number type, currency type .... however all the solutions in this forum discussions, my experiments and elsewhere did not help me. The auto sum just did not work.

For me the numeric data in the column was brought out from a CRM tool and pasted on to excel.
So the entire column of numbers I tried to auto sum had a leading space before the first numeric, you can see that space if you try to align left the data.

If your data is aligned "centre" or with any indent to the left or right you cannot make out the leading or trailing space. Its the leading space that's the culprit.

I manually removed the space before the first digit of the data in the cell and then automatically it become a number with decimal places( because prior to removing the space i had formatted the column to "number type" with 2 decimal places).

Then I used the ASAP excel utility to remove the leading and trailing spaces for all the entries in cells of my range and then immediately the auto sum gave the result at the location where the formula was input.

Please try this on your data and hope this helps in solving your problem.

Thanks & Best Regards,
George Kottackakathu Thomas
[email protected]

**FDibbins** · 09-05-2013, 12:45 AM

Kotshome, nice solution

However, that thread is over 5 years old, I doubt very much they are still monitoring it now (I have done the same myself before though lol)

Also, I have removed your email addy from your post (unless you want to get spammed?)

**Kotshome** · 09-05-2013, 12:56 AM

Hi FDibbins,

Thanks for the reply, nice hearing from the forum.

Well I had first visited this forum for my problem of not being able to use excel then, ever since in all troubles and for new learning I have been reading posts in this forum which has really helped me, in solutions, a lot.

So I thought though this may not help the original posters but help those coming in search for the solutions, as i keep doing .... and being here ...

Thanks for removing my email, it came as part of my auto text key input sequence.

Thanks & Best Regards,
George Kottackakathu Thomas

**maccobb** · 12-05-2018, 05:37 PM

Originally Posted by Kotshome

Hi FDibbins,

Thanks for the reply, nice hearing from the forum.

Well I had first visited this forum for my problem of not being able to use excel then, ever since in all troubles and for new learning I have been reading posts in this forum which has really helped me, in solutions, a lot.

So I thought though this may not help the original posters but help those coming in search for the solutions, as i keep doing .... and being here ...

Thanks for removing my email, it came as part of my auto text key input sequence.

Thanks & Best Regards,
George Kottackakathu Thomas

Hi there! As a very late response to this, it happens to be 5 years from this last post, and I have STILL found this reply incredibly useful. I very much appreciate posts made for posterity's sake.

**FDibbins** · 12-05-2018, 06:24 PM

Originally Posted by maccobb

Hi there! As a very late response to this, it happens to be 5 years from this last post, and I have STILL found this reply incredibly useful. I very much appreciate posts made for posterity's sake.

Thanks for the response, always good to know older posts are still helping