How to calculate total overtime hours in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))Add up only the hours over the limit: =SUMPRODUCT((B2:B8>E1)*(B2:B8-E1)) with the limit (40) in E1.
- Result on the example
- 23.75 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
B2:B8-E1 is each person's hours above 40, which is negative for anyone under. Multiplying by B2:B8>E1 zeros out those rows, so only the extra hours remain: 4.5 + 6 + 1.25 + 12. Dev worked exactly 40 and adds nothing.
=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))Result23.75
B2:B87 rows × 1 col: 44.50, 38.00, 46.00, 40.00, …A range: 7 cells in one column.
E140The value in E1.
B2:B8>E1{7 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The > operator tests greater than.
B2:B8-E1{7 rows × 1 col} 4.5, -2, 6, 0, …The - operator subtracts.
B2:B8>E1)*(B2:B8-E1{7 rows × 1 col} 4.5, 0, 6, 0, …The * operator multiplies.
SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))23.75SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B8>E1)*(B2:B8-E1{7 rows × 1 col} 4.5, 0, 6, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((B2:B8>E1)*(B2:B8-E1)). We ran it in Google Sheets mode on the same data and it returns 23.75.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIF(B2:B8,">"&E1)-E1*COUNTIF(B2:B8,">"&E1) | 23.75 | The hours of everyone over the limit, minus 40 for each of them. No array needed. |
Common mistakes
=SUM(B2:B8)-40*7subtracts the limit from everyone, so people under 40 cancel out others' overtime.- Hours entered as times (44:30) are fractions of a day. Multiply by 24 first, or compare with
E1/24.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate total overtime hours
E2, total the overtime: the hours each employee worked beyond the limit in E1.Questions
How do I show each person's overtime?
Use =MAX(0,B2-$E$1) filled down; it never goes below 0.
How do I calculate overtime pay?
Multiply the overtime by the rate and 1.5: =MAX(0,B2-$E$1)*C2*1.5 with the hourly rate in C2.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.