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]

 

[Tetra201]

Also I am not sure if there is a way to do this same formula in horizontal format.

Yes.

 

[Excellent_one1]

Gotcha. So that is on the right track, but the count needs to reset to zero whenever there is a reset in a row. Also which makes this difficult is that i don't know when the reset will occur. The above is just sample data and should not be expected to always be the same.

The only constants will be the dates(Col A) and column c. But this will be for use in other months.( November, December, ect).

 

[Eric W]

A few questions:

1) What is the reason you don't want to use column E in your formulas?
2) If we agree not to use column E, have you tested the F2 formula from post #4?
3) Will the values in column C always be the same, like they are in your examples?
4) Will you want the sum to reset at a given value, or must it be dependent on "reset" appearing in column B?
5) Will there be multiple "reset"s in a column?
6) Do you want it in a vertical or horizontal format, or both?

 

[Tetra201]

Here is the formula for cell E2 that calculates the cumulative sum based on columns A, B, and C.
The formula works with multiple "reset"s in Column B.

Note: The formula should be entered using Ctrl+Shift+Enter and then copied down.

Caveat 1: There must be the earliest date, a "reset", and an increment in cells A1, B1, and C1, respectively.
Caveat 2: The dates in column A must be in ascending order.

=IF($B2="reset",0,SUM(INDIRECT("C"&MATCH(MAX(IF($B$1:$B2="reset",A$1:$A2)),$A$1:$A2,0)):$C1))

 

[Excellent_one1]

First of I really appreciate the help as I have made no progress on this in a week. However, see response in red below

Here is the formula for cell E2 that calculates the cumulative sum based on columns A, B, and C.
The formula works with multiple "reset"s in Column B.

Note: The formula should be entered using Ctrl+Shift+Enter and then copied down.

Caveat 1: There must be the earliest date, a "reset", and an increment in cells A1, B1, and C1, respectively.
Another catch is that its possible there could be zero "resets" in that entire month. I need to be able to handle a reset that happens on any date, but also no resets.

Caveat 2: The dates in column A must be in ascending order.
The dates in ascending order will always be the case.

=IF($B2="reset",0,SUM(INDIRECT("C"&MATCH(MAX(IF($B$1:$B2="reset",A$1:$A2)),$A$1:$A2,0)):$C1))

 

[Excellent_one1]

Appreciate the help!

A few questions:

1) What is the reason you don't want to use column E in your formulas?
The reason is I will only have columns A, B, and C to create the Sum
2) If we agree not to use column E, have you tested the F2 formula from post #4?
The reason is I will only have columns A, B, and C to create the Sum
3) Will the values in column C always be the same, like they are in your example
Column C will always be the same
4) Will you want the sum to reset at a given value, or must it be dependent on "reset" appearing in column B?so
It must be dependent on a reset. Its possible there could be zero resets or many resets.So the formula must handle both situations
5) Will there be multiple "reset"s in a column
It must be dependent on a reset. Its possible there could be zero resets or many resets.So the formula must handle both situations
6) Do you want it in a vertical or horizontal format, or both?

be
It could be in either format but I think Horizontal would be ideal as I will need to graph these at some point

 

[Tetra201]

Another catch is that its possible there could be zero "resets" in that entire month. I need to be able to handle a reset that happens on any date, but also no resets.

- Why not list all your "catches" at once in your OP?

- The formula works without any "reset"s -- it just keeps adding.

- If you want to reset the sum every month, why not just put a "reset" in column B against every 1st of the month?

From #16: Horizontal would be ideal

The horizontal version can be easily constructed in exactly the same way.

From #12: The above is just sample data and should not be expected to always be the same.
From #16: Column C will always be the same

Isn't it a contradiction?

 

[Tetra201]

Here is a formula with an automatic monthly reset of the cumulative sum to the corresponding column C value.
Note: The formula should be entered using Ctrl+Shift+Enter and then copied down.

=IF(DAY($A2)=1,$C2,IF($B2="reset",0,SUM(INDIRECT("C"&MAX(MATCH(EOMONTH($A2,-1)+1,$A$1:$A2,0),IFERROR(MATCH(MAX(IF($B$1:$B2="reset",A$1:$A2)),$A$1:$A2,0)+1,0))):$C2)))

Any other catches?

 

[Excellent_one1]

No this is fantastic. I really appreciate the help as this is something i have been struggling with for over a week.

Thanks again.

 

[Tetra201]

You are welcome.

If you need a horizontal version please let me know. Personally, I prefer to have dates in a column rather than in a row where they take too much width.