Function Problems

How to Use COUNTIFS in Excel to Count Due, Overdue, and Open Tasks

Maira Mehtab Oct 20, 2026 869 views

Question details

The user needs to track project tasks by counting how many are due today, overdue, or due within specific future date ranges (such as 0-30 or 31-60 days), while strictly excluding tasks that are already completed.

How to Use COUNTIFS in Excel to Count Due, Overdue, and Open Tasks

Product: Excel
Device & OS: not provided
Scenario: Tracking project deadlines and monitoring task statuses using dynamic date formulas in a spreadsheet.
Observed behavior: To accurately count the number of open tasks across different timeframes based on the current date, ensuring that any rows containing a completion date are ignored.

Before you start

Ensure your task tracker has a dedicated column for Due Dates (e.g., Column H) formatted properly as dates, and a Completion Date column (e.g., Column J) that remains completely blank until a task is finalized.

Solution 1Recommended

Apply COUNTIFS with the TODAY Function

Use the COUNTIFS function combined with the TODAY() function to dynamically count tasks based on their due dates while ensuring completed tasks are excluded by checking for blank cells.

The COUNTIFS function allows you to apply multiple criteria to a dataset. By combining it with the TODAY() function, your task tracker will automatically update the task counts every single day without manual adjustments.

To exclude completed tasks from the count, we add a criterion that checks if the completion date column is blank (using ""). The examples below assume your due dates are in H9:H70 and completion dates are in J9:J70.

Count Tasks Due Today

Select the cell where you want to see tasks due today and enter: =COUNTIFS($H$9:$H$70,"="&TODAY(),$J$9:$J$70,"")

Count Overdue (Past Due) Tasks

To see tasks that missed their deadline, enter: =COUNTIFS($H$9:$H$70,"<"&TODAY(),$J$9:$J$70,"")

Count Tasks Due Within 0 to 30 Days

To forecast upcoming tasks for the next month, use a bounded range: =COUNTIFS($H$9:$H$70,">="&TODAY(),$H$9:$H$70,"<"&TODAY()+30,$J$9:$J$70,"")

Count Tasks Due Within 31 to 60 Days

For the subsequent month, shift the TODAY() additions: =COUNTIFS($H$9:$H$70,">="&TODAY()+30,$H$9:$H$70,"<"&TODAY()+60,$J$9:$J$70,"")

Count Tasks Due Over 90 Days from Now

For long-term open tasks, remove the upper bound: =COUNTIFS($H$9:$H$70,">="&TODAY()+90,$J$9:$J$70,"")

Absolute Cell References: These formulas use absolute references (like $H$9:$H$70) to ensure the range doesn't shift if you copy the formula to another cell. Be sure to adjust the column letters and row numbers according to your specific dataset.

Advanced Spreadsheet Formulas

Manage Your Task Trackers Effortlessly with WPS Office

WPS Spreadsheet fully supports advanced array formulas, including COUNTIFS and TODAY(). It is an excellent, free tool for tracking project deadlines, building dashboards, and organizing team tasks with complete Microsoft Excel compatibility.

1. Open your Task Tracker: Launch WPS Spreadsheet and open your existing Excel task tracker workbook.
2. Enter the Formula: Select the cell where you want to display the task count, and type the corresponding COUNTIFS formula from the solution.
3. Press Enter: Hit Enter to calculate the dynamic count. WPS Spreadsheet will instantly update the values every day when you open the file.

100% compatible with Microsoft Excel formulas (.xlsx)Free, lightweight, and fast to load large datasetsBuilt-in templates for project management and task tracking

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return 0 even though there are overdue tasks?

This usually happens if your due dates are stored as text instead of actual dates. Select your date column, right-click, choose Format Cells, and ensure they are formatted as Dates. Additionally, verify there are no hidden spaces in your blank completion date column.

How can I count tasks assigned to a specific person?

You can add another criterion to your COUNTIFS formula. For example, if assignees are in column K, add the assignee range and the specific name to the end of your formula: =COUNTIFS($H$9:$H$70, "<"&TODAY(), $J$9:$J$70, "", $K$9:$K$70, "John").

Do these formulas automatically update tomorrow?

Yes, because the TODAY() function is volatile, it automatically recalculates and pulls the current system date every time you open, edit, or refresh the spreadsheet.

Excel & Data Problems