Running sum reset by zero

[sunflowersherri]

I need to keep a running sum for the last 7 days. If zero is entered (or blank) for a day, then the range should start over on that day... not count the last 7 days. In other words, a day with zero becomes day 1. Is this possible?

[FDibbins]

Hi, welcome to the forum

I take it these are days of the week - are they dates or day numbers perhaps?

[sunflowersherri]

thanks! must admit my brain is a bit fried from trying to figure this out! it's actually number of hours per day, for 7 days. running total cannot be greater than 60 (I have that part figured out). Then, if 34 hours off duty, hourly count starts over at zero... help! my struggle is how to tell Excel to only sum 1 day, then 2, etc up to 7 after a restart.

[FDibbins]

Do you have a sample workbook to share?

[sunflowersherri]

Here is a sample. My formats are inconsistent, but I think this will help illustrate my question. sample.xlsx

[JeteMc]

Try this array formula (activated by Ctrl+Shift+Enter) in C8 and down:

Please Login or Register  to view this content.

To hide the zero values, as was done for C2:C7 you could apply the conditional formatting rule Cell Value = 0 fill and font gray. Here is a copy of your file with the formula and formatting rule applied:
Copy of sample-6.xlsx
Let me know if you have any questions.

[bebo021999]

Try in C8 then drag down:

Please Login or Register  to view this content.

[sunflowersherri]

bebo021999 thank you! this works... I wish I could say I understand the formula, but this appears to be exactly what I need.

[sunflowersherri]

Need help again... can this formula be applied with an 8 day rule (instead of 7)? I thought I would be able to modify the formula you provided, but I'm not sure how to do it...

[JeteMc]

I can modify BeBo's formula to sum the previous eight days, however I didn't attempt to comply with the max 60 rule. The formula would need to be inserted at C9, as opposed to C8, and down. Here is the modified formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

[bebo021999]

@JeteMc: Thx for your update
@sunflowersherri: Does it work for new request of 8 day rule?

[sunflowersherri]

Yes! Thank you both! If you could be so kind, may you offer explanation as to how the formula works?

[bebo021999]

OK.
=IF(A9>=1.4167,0,IF(A8>=1.4167,SUM(B8:B9) (First 2 special cases)
,MIN of 2 cases
(1)define last cell value >=1.4167(SUMPRODUCT(INDEX(B9:INDEX(B2:B8,IFERROR(LOOKUP(2,1/(A2:A8>1.4167),{1,2,3,4,5,6,7}),1)),))
(2)entire range,SUM(B2:B9))))

How (1) define last cell value>=1.4167?
Assume A3=2.5 (>1.4167)
(A2:A8>1.4167) = {FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE}
1/(A2:A8>1.4167) = {#DIV/0,1,#DIV/0,#DIV/0,#DIV/0,#DIV/0,#DIV/0}
LOOKUP(2,1/(A2:A8>1.4167),{1,2,3,4,5,6,7})=2
INDEX(B2:B8,IFERROR(LOOKUP(2,1/(A2:A8>1.4167),{1,2,3,4,5,6,7}),1)),)points to cell B3 (If the is no value>=1.4167,points to B2)
INDEX(B9:INDEX(B2:B8,IFERROR(LOOKUP...)),)refer to range B3:B9
Now (1)equals SUM(B3:B9)
MIN(SUM(B3:B9),SUM(B2:B9)would take which is smaller

Hope it helps