I am modifying a payroll spreadsheet that was developed in MS Excel. The hours worked by each employee have their own row; each cell represents the hours worked on that particular day. I need to isolate ONLY the amount greater than eight hours (for overtime) and sum those quantities at the end of each row for each employee. For example, if an employee works three days overtime in a pay period, each day is two hours overtime, that summary for overtime should be 6 hours at the end of that row (which is a pay period). I am able to isolate any cells that are greater than 8, but that gives me the entire number (i.e. if they worked 10 hours, the formula I used recognizes the whole 10 hours, not just the 2 hours overtime)

Any help would be greatly appreciated,

Regards,

Anthony

Quote:

---

Originally Posted by shoppingeutopia

I am modifying a payroll spreadsheet that was developed in MS Excel. The hours worked by each employee have their own row; each cell represents the hours worked on that particular day. I need to isolate ONLY the amount greater than eight hours (for overtime) and sum those quantities at the end of each row for each employee. For example, if an employee works three days overtime in a pay period, each day is two hours overtime, that summary for overtime should be 6 hours at the end of that row (which is a pay period). I am able to isolate any cells that are greater than 8, but that gives me the entire number (i.e. if they worked 10 hours, the formula I used recognizes the whole 10 hours, not just the 2 hours overtime)

Any help would be greatly appreciated,

Regards,

Anthony

---

you can combine the SUMIF and COUNTIF functions
=SUMIF(B2:F2,">8")-(COUNTIF(B2:F2,">8")*8) where B2:F2 is your range of hours worked

Hope this helped

Quote:

---

Originally Posted by WvR

you can combine the SUMIF and COUNTIF functions
=SUMIF(B2:F2,">8")-(COUNTIF(B2:F2,">8")*8) where B2:F2 is your range of hours worked

Hope this helped

---

Thanks - that was perfect - THIS IS A GREAT SITE!!

Regards

Anthony