Cumulative count formula

Hello,

I hope somebody can assist me.

I want to develop a production tracking sheet based on the simplified test sheet as per attached snip. The data is generated by a batch counter on equipment and at the end of the shift the production staff enter the daily total into the green cells and the sheet then calculates the total daily production by subtracting the counter total from previous day from the current day counter total.

The plan is for the total cumulative monthly and yearly production to be linked to a summary sheet and hopefully to a dashboard.

The problem that I'm faced with is that the running total calculation for month and year, using =SUM (CHILDREN()) formula, is impacted by the fact that the formula for the current day's production shows the previous day's counter reading (-100400 in the example) until such time as the end of shift counter reading is added to the sheet. The daily total will then be correct but the problem then transfers to the next day.

Therefore the cumulative production in any onwards summary or dashboard is is always going to be incorrect.

I hope I've explained the dilemma adequately. Can anybody advise on a solution?

Many thanks in advance.

Regards,

Richie


Best Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓

    Try this

    =IF(ISNUMBER([Production Counter]@row), [Production Counter]@row - [Production Counter]4, 0)

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓

    Hey Richie.

    Couple options for you.

    At first, this sounded like a great use case for a Sheet Summary Field if you wanted to get that data "off" the sheet.

    But after re-reading your post, I think what you're really wanting to fix is [UNITS PRODUCED]2 and [UNITS PRODUCED]3, right? You just want that SUM formula to not include the negative value for those cells?

    If that's the case, then on the [UNITS PRODUCED]2 and [UNITS PRODUCED]3 cells, enter this formula:

    =SUMIF(CHILDREN(), >0)
    

    Let me know if that works!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Answer ✓

    Try this

    =IF(ISNUMBER([Production Counter]@row), [Production Counter]@row - [Production Counter]4, 0)

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    Answer ✓

    Hey Richie.

    Couple options for you.

    At first, this sounded like a great use case for a Sheet Summary Field if you wanted to get that data "off" the sheet.

    But after re-reading your post, I think what you're really wanting to fix is [UNITS PRODUCED]2 and [UNITS PRODUCED]3, right? You just want that SUM formula to not include the negative value for those cells?

    If that's the case, then on the [UNITS PRODUCED]2 and [UNITS PRODUCED]3 cells, enter this formula:

    =SUMIF(CHILDREN(), >0)
    

    Let me know if that works!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!