Results 1 to 4 of 4

Thread: sum = 0 in error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default sum = 0 in error

    I have a column of numbers that sums erroneously to 0. If I add each cell A1+A2+A3, etc it returns the correct value but the sum function always returns 0. I've ensured that all the field formats are numerical. Any ideas what's causing this problem?

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,664
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: sum = 0 in error

    It's still possible to have a number stored as text in a cell formatted as General, or some number format. Try this and see what happens:

    =SUMPRODUCT(A1:A3+0)
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Sep 2016
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sum = 0 in error

    Still returns a 0.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,831
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: sum = 0 in error

    I've ensured that all the field formats are numerical.
    That won't matter if the entries were entered as Text. Changing the field format after it has been entered will have no effect on it.
    What does this formula return?
    =ISNUMBER(A1)

    If it returns FALSE, you have text entries, not numerical ones.
    You can use Text to Columns on column A to convert those all to numbers.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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