Cumulative count formula

Richie

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

SNIP OF TEST PRODUCTION TRACKER .jpg

Paul H

Try this

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

Brett Wyrick

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!

Paul H

Try this

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

Brett Wyrick

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!