How to Use COUNTIFS to Check If a Person Completed All Tasks in Excel
Question details
The user needs to determine if specific individuals have finished all their required tasks by evaluating multiple task status columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking task completion for individuals across multiple task columns in a dataset.
- Observed behavior
- Need a formula that counts records where an individual's tasks are not marked as 'done', effectively isolating incomplete workflows.
Ensure your task tracking table is formatted with clear columns for the person's name and distinct columns for each task status, with consistent terminology like 'done' used throughout.
Using COUNTIFS to Find Incomplete Tasks by Person
Apply the COUNTIFS function with multiple criteria to count rows where a person's tasks are not marked as 'done'.
The COUNTIFS function allows you to apply multiple conditions across different ranges simultaneously. By checking if the task columns do not equal 'done' (<>done), you can easily isolate the tasks that are still pending for a specific person. If the formula returns 0, it means no incomplete tasks were found, confirming all tasks are finished.
Click on the empty cell where you want to display the completion check result.
Type the formula: =COUNTIFS($B$2:$B$6,B9,$C$2:$C$6,"<>done",$D$2:$D$6,"<>done",$E$2:$E$6,"<>done"). Adjust the ranges ($B$2:$B$6 for names, $C$2:$E$6 for tasks) to match your dataset, and replace B9 with the cell containing the target person's name.
Press Enter. If the formula returns 0, the person has completed all tasks. Any number greater than 0 indicates that there are still incomplete tasks assigned to them.
Counting Total Records Assigned to a Person
Use a simpler COUNTIFS or COUNTIF formula to find out the total number of task records assigned to a specific person regardless of their completion status.
Use WPS Spreadsheet to Track Task Completion with COUNTIFS
WPS Spreadsheet fully supports advanced logical and statistical formulas like COUNTIFS, making task tracking, data analysis, and workflow management seamless and efficient.
- 1. Open your dataset: Launch WPS Spreadsheet and open your task tracking document.
- 2. Select the target cell: Click on the cell where you want to calculate the completion statuses.
- 3. Input the formula: Type your COUNTIFS formula. You can use the formula hint tooltips to easily select and lock your data ranges using the F4 key.
- 4. Apply to multiple rows: Press Enter, then drag the fill handle down to apply the formula to all other individuals in your list.
Frequently Asked Questions
Why is my COUNTIFS formula returning a #VALUE! error?
This usually happens if the selected ranges are not the same size. Ensure that your name range (e.g., $B$2:$B$6) has the exact same number of rows as your task ranges ($C$2:$C$6).
Can I use COUNTIFS to count tasks that are exactly 'done' instead?
Yes. Simply replace the "<>done" criteria with "done" in your formula. This will count the number of task columns that have been successfully completed.
Is the COUNTIFS function case-sensitive?
No, the COUNTIFS function is not case-sensitive. It will treat 'Done', 'done', and 'DONE' identically when evaluating the criteria.
What if I have blank cells in my task columns?
If you use the "<>done" criteria, blank cells will be counted because they do not equal 'done'. If you want to exclude blank cells from your incomplete count, you need to add an additional criteria range and criteria like "<>" to ensure it only counts non-blank cells.
