How to count overdue tasks in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done")Count tasks due before today that aren't done: =COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done"), with =TODAY() or an as-of date in G1.
- Result on the example
- 4 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
The first condition keeps tasks with a due date before the as-of date, and the second drops the ones marked Done. Four tasks are late; "Q1 forecast" is due on the as-of date itself, so it isn't overdue yet.
In a live tracker put =TODAY() in G1. A fixed date, as here, is useful for reports that must not change after they are sent.
=COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done")Result4
B2:B1110 rows × 1 col: 3/2/2026, 3/5/2026, 3/9/2026, 3/10/2026, …A range: 10 cells in one column.
G13/16/2026The value in G1.
"<"&G1"<46097"The & operator joins as text.
C2:C1110 rows × 1 col: "Done", "In progress", "Not started", "Done", …A range: 10 cells in one column.
COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done")4COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 3/2/2026, 3/5/2026, 3/9/2026, 3/10/2026, … - criteria1
"<"&G1"<46097" - criteria_range2
C2:C1110 rows × 1 col: "Done", "In progress", "Not started", "Done", … - criteria2
"<>Done"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B11,"<"&G1,C2:C11,"<>Done"). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B11<G1)*(C2:C11<>"Done")) | 4 | The same count as an array test, if you need extra logic such as a priority weight. |
| =COUNTIF(B2:B11,"<"&G1) | 6Different result | Counts everything due before the date, done or not. Also counts the two finished tasks. |
Common mistakes
"<>Done"also counts tasks with a blank status, like "Update policies". That is usually right for overdue work, but filter blanks out if they mean "no task".- TODAY() changes every day, so a report built on it shows a different number tomorrow. Paste the date as a value if you need a snapshot.
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.
Count overdue tasks
G2, count the tasks that were due before the date in G1 and are not marked Done.Questions
How do I count tasks due in the next 7 days?
Use a date window: =COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G1+7,C2:C11,"<>Done").
How do I flag each overdue task?
In a helper column: =AND(B2<$G$1,C2<>"Done"), or use the same test in conditional formatting.
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.