Excel - Formula to sum sequentially but only add like employees

Question

My manager needs to know when her employees (EE) are reaching overtime. There are 80+ EEs on the job site so it is crucial for her to be able to see what day the EEs reach 40 hours. Once I get the Sequential Sum by employee column figured out I will add on straight time and overtime columns but I need to know what day an EE reaches 40 hours first.

This is what I'd like the spread sheet to look like:

EE No   Hours   Date        Sequential Sum
5001    8       7/27/2015   8
5002    8       7/27/2015   8
5003    10      7/27/2015   10
5004    10      7/27/2015   10
5001    8       7/28/2015   16
5002    8       7/28/2015   16
5003    10      7/28/2015   20
5004    10      7/28/2015   20
5001    7       7/29/2015   23
5002    7       7/29/2015   23
5003    10      7/29/2015   30
5004    10      7/29/2015   30
5001    8       7/30/2005   31
5002    10      7/30/2005   33
5003    10      7/30/2005   40
5004    11      7/30/2005   41
5001    10      7/31/2015   41
5002    10      7/31/2015   43
5003    10      7/31/2015   50

The first 3 columns are keyed in by the manager. I'm looking for a formula that will sum cumulatively by employee in the Sequential Sum column. Notice EE 5001 worked 7 hours on 7/29/15, but the cumulative sum for EE 5001 is 23 because 8 hours were worked on 7/27/15 and 7/28/15 each.

I have tried the following without success:

=SUMIF([EE No],"=[@[EE No]]",[Hours])

{=SUM(IF([@[EE No]]=[EE No],[@Hours]))}

I've experimented with OFFSET, FREQUENCY, MATCH SUMIF and SUMIFS unsuccessfully. Any help you can provide with this formula will be greatly appreciated.

I'm not opposed to a VBA solution either so long as I can get the correct data back in the table.

Michael Michael · Accepted Answer · 2015-07-28T22:52:32

I would switch the reference from a table reference to a mix of absolute and relative references. By using an absolute reference for the start of a range and a relative reference for the current row that you are on, you can use the SUMIF equation in Excel

In cell E2, type in the following equation and copy and paste it all the way to the bottom, making sure that you have the $ around the first cell of the range

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

Excel - Formula to sum sequentially but only add like employees

3 Answers