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

Sadia1989

New Member
Joined
Oct 3, 2019
Messages
8
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:

Road_IDSeg_numV1V1cumV2V2cumV3V3cum
11001100
12000100
133535141511
14205572234
15237832526
16280025915
17484328015
18387432116
19390234117
110090034421
111090034627
11217107741027
1133110243128
1140110447230
21220011
22463312
23394746
243122917
2501241329

<tbody>
</tbody>
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

Road_IDSeg_numV1V1cumV1sumV2V2cumV2sumV3V3cumV3sum
1100781125006
1200780125006
13353578141525116
1420557872225346
1523787832525266
162290079910
174693370910
1839947711010
1933202291111
11003200294511
111032002961111
11217202079901111
113333226113
114003466233
21229007116
22469337126
23399477466
24333226113
25003466233

<tbody>
</tbody>


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
 
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
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
ABCDEFGHIJK
1Road_IDSeg_numV1V1cumV1sumV2V2cumV2sumV3V3cumV3sum
21100781125006
31200780125006
413353578141525116
51420557872225346
61523787832525266
7162290079910
8174693370910
91839947711010
1019332322151114
11110032302154514
121110323021561114
13112172023791501114
14113323232111511214
15114023234151521414
162122120013119
172246123313129
182339124713469
1924312122913179
20250121241313299
Cumulative Sums (5)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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