Google Sheets
How to Count Overdue Tasks in Google Sheets
Published by OneCell
Use COUNTIFS to count tasks marked Open with a due date before a date you choose. To count what is overdue today, use today as the cutoff. A task due today stays out of the count until its due date has passed.
Build a six-task example
On a tab called Tasks, put Task in A1, Due date in B1 and Status in C1. Enter each task in column A, its date formula in column B and its status in column C.
Row 2: Send print proof;
=DATE(2026, 11, 3); Open.Row 3: Confirm pickup time;
=DATE(2026, 11, 4); Done.Row 4: Review venue note;
=DATE(2026, 11, 5); Open.Row 5: Draft supply list; leave B5 empty; Open.
Row 6: Check sign dimensions;
=DATE(2026, 11, 6); Open.Row 7: Approve label copy;
=DATE(2026, 11, 4); Open.
In D1, enter As of. In E1, enter =DATE(2026, 11, 5). Using DATE makes the cutoff and due dates unambiguous, regardless of how the sheet displays them.
Count the tasks due before the cutoff
In D2, enter Overdue. Paste this formula into E2:
=COUNTIFS(B2:B7, ">0", B2:B7, "<"&$E$1, C2:C7, "Open")
COUNTIFS counts a row only when it passes all three checks. The due date must be positive, which leaves out blank, zero and negative values in this setup. It must also be strictly earlier than E1, and the status must be Open. With November 5 in E1, expect 2: rows 2 and 7. The other rows are done, due that day, undated or due later.
Check what changes the result
Change C2 from Open to Done. Expect 1. Change C2 back to Open.
Change E1 to
=DATE(2026, 11, 6). Expect 3: row 4 now counts, while row 6 is due on the cutoff date and still stays out.Change E1 to
=DATE(2026, 11, 3). Expect 0. Even the November 3 task is not earlier than this cutoff.
To check the date guard, reset E1 to November 5 and enter 0 in B5. The result should stay at 2. Clear B5 again before you continue.
Switch to today
Replace E1 with =TODAY() and leave E2 as it is. TODAY returns the date without a time, as of the sheet’s last recalculation. A fixed cutoff is useful for checking the formula, but it still uses the statuses currently in your list. It cannot recover which tasks were open on a past day.
Use date values without times in column B, and leave undated tasks blank. The positive check is not a date validator for imported text or stray numbers. Keep the status labels consistent: a row marked Waiting will not count unless you change the rule to include it. If you add rows beyond row 7, extend both B ranges and the C range to the same last row. The count covers matching rows in those ranges, even if you hide or filter them out of view.
If you are totaling a number column and want that total to follow the filter on your sheet, see how to sum only the visible rows in Google Sheets.