# How to calculate total overtime hours in Excel

Add up only the hours over the limit: `=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))` with the limit (40) in E1.

**Excel:** `=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Employee | Hours this week |  | Overtime after | 40 |
| Ana | 44.5 |  | Total overtime |  |
| Ben | 38 |  |  |  |
| Cara | 46 |  |  |  |
| Dev | 40 |  |  |  |
| Eli | 41.25 |  |  |  |
| Fay | 52 |  |  |  |
| Gus | 36.5 |  |  |  |

Formula in E2: `=SUMPRODUCT((B2:B8>E1)*(B2:B8-E1))`

Result: 23.75

## 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.

## Other ways

- `=SUMIF(B2:B8,">"&E1)-E1*COUNTIF(B2:B8,">"&E1)`: The hours of everyone over the limit, minus 40 for each of them. No array needed.

## Common mistakes

- `=SUM(B2:B8)-40*7` subtracts 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`.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/total-overtime-hours