How to Use COUNTIFS to Track Pending and Completed Team Tasks
Question details
The user needs to count the number of pending and completed tasks for different teams in a task tracker.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a team task tracker and needing automated metrics to calculate pending vs. completed assignments per team.
- Observed behavior
- The goal is to accurately calculate the totals for pending (indicated by a blank status or date) and completed (indicated by a non-blank status or date) assignments using spreadsheet formulas.
Ensure your task tracker data is organized with clear columns for team names and task statuses, and verify there are no hidden spaces in seemingly blank cells before applying formulas.
Use the COUNTIFS Function to Evaluate Blank and Non-Blank Cells
The most efficient way to count rows meeting multiple criteria, such as a specific team name and a blank or non-blank completion status.
The COUNTIFS function evaluates multiple conditions in your dataset. By checking for empty strings ("") you can identify pending tasks, and by checking for non-empty cells ("<>") you can calculate completed tasks.
Click on the summary cell where you want to display the pending task count for the first team (e.g., B11).
Type `=COUNTIFS($B$2:$B$6,$A11,$D$2:$D$6,"")` and press Enter. This counts tasks for the team in A11 where the status column (D) is completely blank.
Select the adjacent cell (e.g., C11) and enter `=COUNTIFS($B$2:$B$6,$A11,$D$2:$D$6,"<>")` to count tasks where the status column is not empty.
Select cells B11 and C11, then click and drag the fill handle (the small square at the bottom-right corner) down to apply these formulas to the rest of the teams in your summary table.
Use the SUMPRODUCT and LEN Functions as an Alternative
A robust alternative if you need to calculate based on the character length of the cell contents, which helps bypass issues with hidden spaces.
Effortlessly Manage Advanced Formulas with WPS Office
WPS Spreadsheet fully supports COUNTIFS, SUMPRODUCT, and all advanced Excel functions, making it simple to build powerful task trackers and team dashboards.
- 1. Open your tracker: Launch WPS Spreadsheet and open your team task tracker workbook.
- 2. Insert the formula: Click on the cell for your summary totals and type `=COUNTIFS`.
- 3. Use the formula prompt: Follow the built-in syntax prompt to select your criteria ranges and conditions (like "" or "<>") effortlessly.
- 4. Drag to apply: Press Enter to calculate, then drag the fill handle to apply the formula across your entire team summary table.
Frequently Asked Questions
Why is COUNTIFS returning 0 for completed tasks even though cells look empty?
The cells might contain invisible characters, hidden spaces, or formulas returning empty text. You can use the TRIM function to clean your data, or switch to the SUMPRODUCT method using a LEN check to bypass hidden spaces.
Can I use COUNTIFS to check data across different worksheets?
Yes. You can reference ranges in other worksheets by selecting the range directly while writing the formula, or by typing the sheet name followed by an exclamation mark before the range (e.g., Sheet2!$B$2:$B$6).
How do I count tasks that specifically say 'In Progress' instead of being blank?
Simply replace the blank criteria (" ") with your specific text in quotes. For example, your COUNTIFS formula would look like this: `=COUNTIFS($B$2:$B$6,$A11,$D$2:$D$6,"In Progress")`.
Is there a limit to the number of criteria I can use in a COUNTIFS formula?
The COUNTIFS function can handle up to 127 pairs of criteria ranges and criteria in modern spreadsheet applications, providing you with plenty of flexibility for creating highly complex trackers.
