SUM cells with formulas not working

Eva M
Eva M
edited 12/09/19 in Formulas and Functions

Hello, I have a sheet with a column of cells with formulas in them. These formulas produce numbers. Then I have a second sheet, where I have a formula in a cell that references the first sheet's column with numbers. This formula in the second sheet is asking to SUM some of the numbers in the first sheet's column and divide it by their COUNT. So, for example, I am asking to reference Sheet 1, and sum 2+3+4+5+6 and divide that by 5. However, the totals are coming out wrong. I saw in another post that a person was saying that if one tries to SUM cells with a formula in them, the SUM will be wrong. Is this the case? I only have numbers, no text in the cells. Thank you!

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Eva,

    Can you maybe share some screenshots and what formulas you're using? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    "I saw in another post that a person was saying that if one tries to SUM cells with a formula in them, the SUM will be wrong. Is this the case?"

     

    This is not necessarily true. There are a handful of different variables that come into play to include the formula producing the numbers, the data the numbers are being pulled from, column types, etc.

    thinkspi.com

  • Eva M
    Eva M
    edited 06/25/19

    Hi Andree, Thanks so much for the quick response! I am attaching screenshots of my two sheets. I didn't have any formal training with Smartsheet so I've been self-teaching. It's not that easy considering what my boss wants from me. :-)

    Now that I see the screenshots, is it too small to read or can you enlarge it? 

    Smartsheet help.jpg

  • SO I just figured out that it actually is working properly - I was just including the wrong days in my own calculations. I should have excluded June 17 and 18 from my addition because today is the 25th...so 7 days worth of numbers for the report would be 25th, 24th, 23rd, 22nd, 21st, 20th, and the 19th. Wheeew. OMG. So my formulas are actually correct and I don't have a question anymore. Now I feel dumb. I guess I needed to write it out somewhere to arrive to this conclusion. Thank you anyway for trying to help!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Easy to miss!

    Glad you got it working!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭✭

    I am having issues with sum not working with formula fields. The answer just returns 0. 

     

     

    Annotation 2019-10-23 104539.png

    Annotation 2019-10-23 104603.png

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your numbers are actually being stored as text. Can you provide the formula that is returning the numbers you are trying to sum?

    thinkspi.com

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭✭

    Brilliant! As soon as you said that I realized I had " " around my return values. Once I took those out, the formula worked.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent. Glad you got it working. yes

    thinkspi.com

  • Kyle Walker
    Kyle Walker ✭✭✭✭✭

    Having the same problem, I assume it is because my number might show as text but not sure how to fix that.

  • Kyle Walker
    Kyle Walker ✭✭✭✭✭
  • I'm having this same issues. I've built a summary table to count the number of "Issues" that meet certain criteria. I'm using a the COUNTIFS formula to do this.

    This summary sheet feeds a dashboard that I use pie chart on to visually show the breakdown of Issue categories. Piecharts show an error in Smartsheet when they have a zero value to display, so I am using a workaround that someone else posted to have a category that has a count of 1, when everything else Sums to zero, I'm using a simple =(IF(SUMA1:A2)=0,1,0) formula for this.

    The problem is that it seems to pull a SUM of 0, even with the COUNTIFS formulas are calculating a value that is greater than zero

    Any advice?