Cumulative sum that restarts count on condition

E

Excellent_one1

New Member
Joined
Oct 25, 2016
Messages
15
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]
 
Sort by date Sort by votes
Welcome to the forum.

Maybe:

ABCDE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Cumulative count[/TD]
[TD="bgcolor: #FAFAFA"]Goal[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/1/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/2/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/3/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/4/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/5/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/6/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/7/2016[/TD]
[TD="bgcolor: #FAFAFA"]reset[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/8/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/9/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(B2="reset",0,SUM(E1)+C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for quick response. The problem with that is it will set the count to zero, but it will pick up where it left off. I basically need column E to populate using the other columns.
 
Upvote 0
I'm afraid I don't understand your objection. The formula I showed matched your goal exactly. If you don't want to use the subtotals above the formula in column E, then you could do something like this:

ABCDEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Cumulative count[/TD]
[TD="bgcolor: #FAFAFA"]Goal[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/1/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/2/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/3/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/4/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/5/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/6/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/7/2016[/TD]
[TD="bgcolor: #FAFAFA"]reset[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/8/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/9/2016[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="align: right"]12[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(B2="reset",0,SUM(E1)+C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=SUM($C$2:$C2)-IFERROR(SUM($C$2:OFFSET($C$1,LOOKUP(2,1/($B$2:$B2="reset"),ROW($B$2:$B2)-ROW($B$2)+1),0)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



But the formula is much more complicated, and will take longer to calculate, and gives you the exact same result. What am I missing?
 
Upvote 0
GetFileAttachment
 
Upvote 0
Assuming your step (column C) is always the same across the data series,
here is the formula that does not rely on any columns at all.
Put the formula in cell E2 and copy down.

Step (e.g., 2) and CutOff (e.g., 12) may be named cell or just named values.

=MOD((ROW()-ROW(E$1))*Step,(Step+CutOff))
 
Last edited:
Upvote 0
Thanks for response, however I am confused on what the arguments should be.

What is e.g., 2? and cutoff?

=MOD((ROW()-ROW(E$1))*C2,(C2+CutOff))
What would CutOff be?
 
Upvote 0
Also I am not sure if there is a way to do this same formula in horizontal format.
[TABLE="width: 1181"]
<tbody>[TR]
[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]10/2/2016[/TD]
[TD="align: right"]10/3/2016[/TD]
[TD="align: right"]10/4/2016[/TD]
[TD="align: right"]10/5/2016[/TD]
[TD="align: right"]10/6/2016[/TD]
[TD="align: right"]10/7/2016[/TD]
[TD="align: right"]10/8/2016[/TD]
[TD="align: right"]10/9/2016[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]reset[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]reset[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

zoharb
  • Question Question
Has to create dropdown
Replies
0
Views
110
zoharb
zoharb
R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
89
rk_rk
R
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
509
shg
shg
S
  • Solved
Five Values that Equal a Sum Value
2
Replies
12
Views
225
ststern45
S
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
168
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,226,840
Messages
6,193,278
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top