+ Reply to Thread
Results 1 to 10 of 10

Issue with seemingly simple SUM formula

  1. #1
    Registered User
    Join Date
    10-12-2012
    Location
    OKC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Issue with seemingly simple SUM formula

    Morning guys,

    I'm having an issue with a formula I've used many times without a problem. I have a SUM formula that needs to calculate a row of numbers. The row of numbers are also generated from a formula. The SUM comes out as 0 every time unless I manually type a number into the row i'm trying to calculate. It seems that it will not add up the rows so long as the numbers are generated from another formula. It's probably a setting or something simple. This is Excel 2013 btw. Thanks for any help!

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Issue with seemingly simple SUM formula

    Does the "other formula" include LEFT, MID or RIGHT, function? If yes, this formula in fact gives you a text. Not a number..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    10-12-2012
    Location
    OKC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Issue with seemingly simple SUM formula

    =IF(D10<=20,$D$9,0)

    This is the other formula. The cell it's on is centered and merged, but there's nothing unusual with it. The result of the above formula is 5. If I were to scrap the formula in the same cell and just type 5, it would calculate on my sum line. I use this same scenario without issue on another similar spreadsheet, only it was created in Excel 2003 rather than 2013. but I don't think 2013 is the overall issue.

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Issue with seemingly simple SUM formula

    If D9 is a number i don't see the reason that your SUM formula is not working. Me too, as you, don't believe that is an issue of Excel 2013...

    Perhaps to upload a small sample workbook showing to us the problem??

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Issue with seemingly simple SUM formula

    Is D9 a number or text? I assume that's the cell that's got a 5 in it.

    Test it with =ISNUMBER(D9)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    10-12-2012
    Location
    OKC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Issue with seemingly simple SUM formula

    Odd, I never knew about that test. It says FALSE. However, in the formatting, D9 is listed as a number.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Issue with seemingly simple SUM formula

    Hi,

    Formatting is irrelevant as far as using cell values in formulae is concerned.
    The important thing is what does the cell actually contain, not what it looks like. A cell can have many exotic presentations but none of them alter the underlying value.

    You've confirmed D9 is not a number (an =ISTEXT(D9) presumably gives TRUE) so if D9 is a formula wrap it in an =VALUE(your_formula) function.
    If it's not a formula then make sure it IS a number by formatting the cell as NUMBER and entering the value again.

  8. #8
    Registered User
    Join Date
    10-12-2012
    Location
    OKC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Issue with seemingly simple SUM formula

    Hmm, =VALUE made the cell SUM function as intended. It seems that the problem is solved, however, I have never had to use =VALUE before for the same kind of scenario. I wonder what the underlying cause is for this spreadsheet in particular? I'd be happy to hear your opinions on why I need to start using =value all of a sudden. The only difference is I just started using Excel 2013 this week. If you all would prefer not to get into why or why not an =value prefix is needed, i'm content with the first issue being solved. Thank you so much for your help!

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Issue with seemingly simple SUM formula

    How is the data in D9 obtained? I"m guessing it's imported or copied from another program? Go to the formula cell, there may be spaces before or after the number? Or, the cells were originally formatted as text and were converted to number without accessing the cell directly (i.e. changed the entire column).

    Alternately to using VALUE, you could select the column and do "text to columns" (data tab). (just click Finish and Excel will (maybe) re-evaluate the numbers.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  10. #10
    Registered User
    Join Date
    10-12-2012
    Location
    OKC
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Issue with seemingly simple SUM formula

    I suspected that it was because the original formula was in fact imported from another spreadsheet and I may have mucked up the paste function.

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Issue with seemingly simple SUM formula

    Hi,

    I'd be extremely surprised if it was merely using Excel 2013 for the first time that was the problem. But if that was indeed the ONLY change, i.e. you have not imported any data to D9 since the change I'd be interested to see the original workbook so that we can try and replicate the problem with an older version.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Issue with seemingly simple SUM formula

    Hi,

    I'd be extremely surprised if it was merely using Excel 2013 for the first time that was the problem. But if that was indeed the ONLY change, i.e. you have not imported any data to D9 since the change I'd be interested to see the original workbook so that we can try and replicate the problem with an older version.

+ 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