Excel - How do I create a cumulative sum column within a group?

Question

In Excel, I have an hours log that looks like this:

PersonID   Hours   JobCode
1          7       1
1          6       2
1          8       3
1          10      1
2          5       3
2          3       5
2          12      2
2          4       1

What I would like to do is create a column with a running total, but only within each PersonID so I want to create this:

PersonID   Hours   JobCode    Total
1          7       1          7 
1          6       2          13
1          8       3          21
1          10      1          31
2          5       3          5
2          3       5          8
2          12      2          20
2          4       1          24

Any ideas on how to do that?

score 12 · Accepted Answer · answered Feb 18 '16 at 23:55

12

In D2 and fill down:

=SUMIF(A$2:A2,A2,B$2:B2)

answered Feb 18 '16 at 23:55

Tim Williams

154,628
8
97
125

DeanOC · Answer 2 · 2016-02-18T23:39:19.723

2

Assuming that your data starts in cell A1, this formula will accumulate the hours until it finds a change in person ID.

=IF(A2=A1,D1+B2,B2)

Put the formula in cell D2, and copy down for each row of your data.

edited Feb 18 '16 at 23:39

answered Feb 18 '16 at 23:33

DeanOC

7,142
6
42
56

You may want to also declare that it requires that the data be sorted or it will restart the sum. – Scott Craner Feb 19 '16 at 00:33
1

@ScottCraner yeah, I was sorta assuming that based on the OPs example – DeanOC Feb 19 '16 at 01:12

Excel - How do I create a cumulative sum column within a group?

2 Answers2