Cumulative sum that restarts count on condition

[Excellent_one1]

Hi all,

Below is a copy of the data. I need to have a cumulative sum of column b, that resets the count to zero if i encounter a reset condition.

The problem is I do not know when the reset(s) will occur( It is not a fixed location), so the challenge is catching the moving target.
Also the goal column is what I would like to have shown.

[TABLE="width: 337"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="width: 385"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cumulative count [/TD]
[TD] Goal[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10/2/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]10/3/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]10/4/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]10/5/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]10/6/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]10/7/2016[/TD]
[TD]reset[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10/8/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10/9/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Excellent_one1]

How difficult would it be to continue the count and not reset at the end of the month.For example the count could be at 42 on 10/31/2016 and assuming no reset action on 11/1/2016 and a column C value of 5 the runnning total would be 47.

 

[Tetra201]

In this case, use my formula from Post #14.

I ran a successful test with the following parameters:

A1:A33 filled with dates from 9/30/2016 to 11/1/2016
“reset” in B1, B8, and B11
C1:C31 filled with 2; C32 has 5
The formula entered in E2 using Ctrl+Shift+Enter and copied down

Result: a sum of 42 for 10/31/16 and 47 for 11/1/2016

 

[Excellent_one1]

That does the trick, but if there are no resets prior I am getting NA values. I need those values to be summed as well.

[TABLE="width: 314"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]10/2/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]10/3/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]10/4/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]10/5/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]10/6/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]10/7/2016[/TD]
[TD]reset[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10/8/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10/9/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

As I wrote in Post #14:

Caveat 1: There must be the earliest date, a "reset", and an increment in cells A1, B1, and C1, respectively.

So, insert | 9/30/2016 | reset | 2 | just between the Date/Goal and 10/1/2016 rows.
Then enter the following formula in E3 using Ctrl+Shift+Enter and copy down.

=IF($B3="reset",0,SUM(INDIRECT("C"&MATCH(MAX(IF($B$1:$B3="reset",A$1:$A3)),$A$1:$A3,0)):$C2))

 

[Excellent_one1]

Makes sense and works like a charm thanks again. May I ask how you became so proficient in this? Are you a programmer by trade?

 

[Tetra201]

You are welcome. Glad it works for you.

And no, I not a programmer, although I used to write codes in FORTRAN and assembler many years ago.

 

[Excellent_one1]

New problem discovered.

I have these sets of data side by side and it seems the formula is taking into account the previous data set instead of being independent of the first data set.
. Example the second goal column should be counting 3, 6, 9,12,ect.
[TABLE="width: 520"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Reset Column[/TD]
[TD]Per Day[/TD]
[TD]Goal[/TD]
[TD]Reset Column[/TD]
[TD]Per day[/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2016[/TD]
[TD]reset[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]reset[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2016[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10/2/2016[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]10/3/2016[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]10/4/2016[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]10/5/2016[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]

 

[Excellent_one1]

Silly mistake on my end disregard.