+ Reply to Thread
Results 1 to 10 of 10

Excel 2016 - SUM Function Doesn't Add but + Does

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Excel 2016 - SUM Function Doesn't Add but + Does

    I'm working on a worksheet where a simple addition problem isn't working.

    Cell H11 contains the number 1. Cell K11 contains the number 1.

    In cell L11 is the formula =SUM(H11,K11). The result of this formula is 1.

    If I change the formula in L11 to =H11+K11, the result is 2.

    I always try to use =SUM() but I may have to revert to the older +.

    Any thoughts?

    Eric

  2. #2
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Agawam, MA
    MS-Off Ver
    Microsoft Office 365
    Posts
    708

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    sounds like cell H11 or K11 is in fact text and not a number. see if there is a little green mark in the top left hand corner of either of these cells. If so click on the cell and then click on the yellow Exclamation mark. Select convert to number
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,591

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    The SUM() function ignores text -- including numbers stored as text. The + operator cannot ignore text, so it coerces numbers stored as text to numbers. My guess -- without being able to see your workbook -- is that one of those 1s is the text string "1" and not the number 1. I would test with the ISTEXT() function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    07-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    Thank you for the quick responses.

    dosydos...there's nothing to indicate any of the cells to be text. Selecting each cell shows that it's formatted as a number. No yellow exclamation point.

    MrShorty...I ran the ISTEXT() function. Lo and behold, cell K11 is text. So now the question becomes, how is it text?

    As I mentioned above, the cell is formatted as a number. That being said, cell K11 is a formula:

    Please Login or Register  to view this content.
    Is the problem caused by having the "N/A" in the formula?

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,975

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    What's in K15 on the "Weekly Hours" sheet?

  6. #6
    Registered User
    Join Date
    07-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    Another formula:
    Please Login or Register  to view this content.
    J15 is a formula:
    Please Login or Register  to view this content.
    D15 through I15 are all numbers that are manually entered.

    All cells are formatted as numbers.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,591

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    Note the quotation marks in your IF() function -- "3.00" or "2.00". These are text strings, not numbers. Remove the quotation marks from those values in the IF() function so that they are seen as numbers and not text.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,975

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    That formula is inserting text values, not numbers. You need to remove the quotes.

  9. #9
    Registered User
    Join Date
    07-23-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    And that was it. Thank you for the assist!

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,975

    Re: Excel 2016 - SUM Function Doesn't Add but + Does

    You're welcome & thanks for the feedback

+ 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