+ Reply to Thread
Results 1 to 10 of 10

Why is auto sum not working

  1. #1
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220

    Why is auto sum not working

    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?

  2. #2
    Forum Contributor
    Join Date
    07-09-2008
    Location
    Dallas
    MS-Off Ver
    2007
    Posts
    121
    You sure its not set to 'count' (you have 30 items, looks like 3, hehe) or something other than sum (min, max, etc)?

  3. #3
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    Post a .zip sample of the workbook.

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

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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)

  5. #5
    Forum Contributor
    Join Date
    07-10-2008
    Location
    UK
    Posts
    220

    thanks

    thanks that way worked.. all gd now

  6. #6
    Registered User
    Join Date
    11-20-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Why is auto sum not working

    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
    xxxxxxxx@xxxxx.com
    Last edited by FDibbins; 09-05-2013 at 12:45 AM. Reason: for clarity

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    Re: Why is auto sum not working

    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?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    11-20-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Why is auto sum not working

    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

  9. #9
    Registered User
    Join Date
    12-05-2018
    Location
    Mooresville, IN
    MS-Off Ver
    2010
    Posts
    1

    Re: Why is auto sum not working

    Quote Originally Posted by Kotshome View Post
    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.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    Re: Why is auto sum not working

    Quote Originally Posted by maccobb View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1