Cumulative Sum untill a value reached and reset

[Sadia1989]

I have a data set look like

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Road_ID[/TD]
[TD]V1[/TD]
[TD]V1cum[/TD]
[TD]V2[/TD]
[TD]V2cum[/TD]
[TD]V3[/TD]
[TD]V3cum[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]13[/TD]
[TD]4[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]14[/TD]
[TD]0[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

each Road_ID has several rows. V1cum, V2cum and V3cum are cumulative sum of V1, V2 and V3 respectively which starts from zero after finishing each Road_ID. I need to restart cumulative sum after reaching 5 in V1cum and after finishing a Road_ID.

My expected result is
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Road_ID[/TD]
[TD]V1[/TD]
[TD]V1cum[/TD]
[TD]V2[/TD]
[TD]V2cum[/TD]
[TD]V3[/TD]
[TD]V3cum[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Please help me reaching this result.

Thanks,
Sadia

 

[Peter_SSs]

Welcome to the MrExcel board!

Hopefully you just made a mistake with the final expected value for V2cum as it follows a number greater than 5?

Try this copied down then copy that whole column of formuls to V2cum and V3cum

Excel Workbook
	A	B	C	D	E	F	G
1	Road_ID	V1	V1cum	V2	V2cum	V3	V3cum
2	1	2	2	0	0	1	1
3	1	4	6	3	3	1	2
4	1	3	3	4	7	4	6
5	1	3	6	2	2	1	1
6	2	0	0	4	4	2	2
7	2	3	3	3	7	2	4
8	2	4	7	1	1	3	7
9	2	1	1	3	4	1	1
10	2	0	1	2	6	4	5
11	2	2	3	1	1	0	5
Cumulative Sums

 

[Sadia1989]

I have a data set of roadway and roadway segments vehicle count for calculations. Data set is just like before. But I changed the values for better understanding.

Data set contains

Road_ID= ID of each road
Seg_num= Index of each segment in each Road_ID
V1= Vehicle counts of type 1 in each Seg_num
V2= Vehicle counts of type 2 in each Seg_num
V3= Vehicle counts of type 3 in each Seg_num
V1cum= Cumulative sum of V1 by Seg_num and Road_ID,
V2cum= Cumulative sum of V2 by Seg_num and Road_ID,
V3cum= Cumulative sum of V3 by Seg_num and Road_ID.
Data frame looks like:

[TABLE="width: 338"]
<tbody>[TR]
[TD]Road_ID[/TD]
[TD]Seg_num[/TD]
[TD]V1[/TD]
[TD]V1cum[/TD]
[TD]V2[/TD]
[TD]V2cum[/TD]
[TD]V3[/TD]
[TD]V3cum[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
I need to restart cumulative sum in V1cum, V2cum and V3cum after reaching 5 in all these (V1cum, V2cum and V3cum) columns and also when starting a new Road_ID. And get the cumulative sum values from V1cum, V2cum and V3cum before restarting the cumulative sum and place in each row of that range in separate column named, V1sum, V2sum and V3sum.

My expected result is

[TABLE="width: 563"]
<tbody>[TR]
[TD]Road_ID[/TD]
[TD]Seg_num[/TD]
[TD]V1[/TD]
[TD]V1cum[/TD]
[TD]V1sum[/TD]
[TD]V2[/TD]
[TD]V2cum[/TD]
[TD]V2sum[/TD]
[TD]V3[/TD]
[TD]V3cum[/TD]
[TD]V3sum[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


Please help me reaching this result. V1cum, V2cum, V3cum columns are for better understanding, but I need only columns V1sum, V2sum and V3sum columns.

Thanks in advance.

Sadia

 

[Peter_SSs]

Post 3 is a duplicate to https://www.mrexcel.com/forum/excel-questions/1111567-group-sum-each-row-according-some-condition-set-columns.html

Please do not post the same question multiple times. Refer to #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, follow-ups or answers to post 3 should be posted to the linked thread.