Excel 2013, Adding up a group of employee over time hours for 1 week

1boredguy

New member
Joined
Feb 28, 2012
Messages
8
Reaction score
0
Points
0
Row A has employee names
Row B through H (irrelevant) lists the hours worked per day.
Row I has the total hours worked per week PER employee.

How do I build this formula?
End goal, I want everyone's overtime, qty hours worked over 40, to be totaled for that week.
I'm thinking something like... IF I1 is greater than 40, then I1 minus 40... ELSE ignore I1... then move to I2, I3, I4, I5, etc. and add them all up in one formula.

BTW: this formula will be used with different groups. Some groups have 5 employees. Some groups have 10 or more employees.

thanks!
 
Last edited:
Please post a sample sheet showing data and expected results. Thanks
 
in J2 perhaps:
=MAX(0,I2-40)
and copy down as far as necessary, then add a sum of that column at the bottom.
(This assumes wherever you've said 'row' you meant 'column'.)

If you just want a single cell containing the sum of all overtime hours you can array-enter (Ctrl+Shift+Enter not just Enter) the following formula in any cell (not column I):
=SUM(IF(I2:I1000>40,I2:I1000-40,0))
Here I've used the rows 2 to 1000, so make sure that it covers the likely maximum number of employees and there isn't other stuff in those cells (I2:I1000).
 
Thank you for looking. I was aiming for a schedule - such as a formula that creates a pattern. This is quite helpful though.
 
I echo Pecoflyer's request/comment in msg#2.
 
in J2 perhaps:
=MAX(0,I2-40)
and copy down as far as necessary, then add a sum of that column at the bottom.
(This assumes wherever you've said 'row' you meant 'column'.)

If you just want a single cell containing the sum of all overtime hours you can array-enter (Ctrl+Shift+Enter not just Enter) the following formula in any cell (not column I):
=SUM(IF(I2:I1000>40,I2:I1000-40,0))
Here I've used the rows 2 to 1000, so make sure that it covers the likely maximum number of employees and there isn't other stuff in those cells (I2:I1000).

Thanks guys!
Yes, I meant "column" earlier.
This =SUM(IF idea helped me get where I wanted.

Example below:
=SUM(IF(I5>40,I5-40,0),IF(I7>40,I7-40,0),IF(I9>40,I9-40,0),IF(I11>40,I11-40,0),IF(I13>40,I13-40,0),IF(I15>40,I15-40,0))
 
Back
Top