Group sum in each row according to some condition set in columns

[Sadia1989]

I have a data set of roadway and roadway segments vehicle count calculations.
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

 

[Sadia1989]

Hi Peter,

I appreciate your previous help. Sorry for replying after a long time.

I meant the green and blue colored portion (in your previous reply #8 ) as the end segment. The summing range would be same for V1sum, V2sum and V3sum. Therefore, even though the blue cells sum 4+2=6, it cannot be sum starting from 0. Because the other two sums in the same range did not reach 5 (green cells).

Thanks,

Sadia

 

[Peter_SSs]

This seems to produce the results for the samples given, but I'm not particularly confident that I have understood well enough that it will work for all data circumstances. If not please post another small sample that demonstrates any failure and provide further explanation regarding such failure.

These 2 formulas copied down and then the columns of formulas copied to the other corresponding columns.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Road_ID	Seg_num	V1	V1cum	V1sum	V2	V2cum	V2sum	V3	V3cum	V3sum
2	1	1	0	0	78	1	1	25	0	0	6
3	1	2	0	0	78	0	1	25	0	0	6
4	1	3	35	35	78	14	15	25	1	1	6
5	1	4	20	55	78	7	22	25	3	4	6
6	1	5	23	78	78	3	25	25	2	6	6
7	1	6	2	2	9	0	0	7	9	9	10
8	1	7	4	6	9	3	3	7	0	9	10
9	1	8	3	9	9	4	7	7	1	10	10
10	1	9	3	3	23	2	2	15	1	1	14
11	1	10	0	3	23	0	2	15	4	5	14
12	1	11	0	3	23	0	2	15	6	11	14
13	1	12	17	20	23	7	9	15	0	11	14
14	1	13	3	23	23	2	11	15	1	12	14
15	1	14	0	23	23	4	15	15	2	14	14
16	2	1	2	2	12	0	0	13	1	1	9
17	2	2	4	6	12	3	3	13	1	2	9
18	2	3	3	9	12	4	7	13	4	6	9
19	2	4	3	12	12	2	9	13	1	7	9
20	2	5	0	12	12	4	13	13	2	9	9
Cumulative Sums (5)