Excel Formula to Count Defects That Are Not Closed or Deferred
Question details
The user needs an Excel formula to count records excluding two specific statuses.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Counting specific data records in a spreadsheet while applying multiple exclusion criteria.
- Observed behavior
- Needs to calculate a total count of defects without including rows marked as either Closed or Deferred.
Ensure your defect tracking data is organized in a clear column format without merged cells, and identify the exact column containing the status values.
Use the COUNTIFS Function for Multiple Exclusions
The standard and most compatible way to count cells that do not match multiple criteria.
The COUNTIFS function allows you to apply multiple conditions to a dataset. By using the not equal to operator (<>), you can instruct Excel to skip counting rows that contain specific words.
Click on the empty cell where you want the final defect count to be displayed.
Type the formula =COUNTIFS('All Defects'!I:I,"<>Closed",'All Defects'!I:I,"<>Deferred"). Adjust 'All Defects'!I:I to match the actual sheet name and column where your defect statuses are located.
Press Enter to execute the formula. Excel will display the total number of defects excluding the closed and deferred ones.

Use LET and SUM for Dynamic Array Counting
An advanced method using modern dynamic array functions to filter and count statuses efficiently.
Easily Track and Count Defect Data in WPS Spreadsheet
WPS Spreadsheet provides robust formula support, including COUNTIFS and array functions, allowing you to efficiently filter, track, and count your project defects without hassle.
- 1. Open your workbook: Launch WPS Spreadsheet and open your defect tracking workbook.
- 2. Select a cell: Click on the cell where you want to display the active defect count.
- 3. Insert formula: Go to the Formulas tab or type directly to insert the =COUNTIFS formula with your exclusion criteria.
- 4. View results: Press Enter to instantly view your filtered data counts.

Frequently Asked Questions
Can I exclude a third status using the COUNTIFS method?
Yes, you can add another criteria range and criteria pair to the COUNTIFS formula, such as adding ,'All Defects'!I:I,"<>Pending" to the end of your existing formula.
Why is my COUNTIFS formula returning a 0 or an error?
Ensure that the sheet name ('All Defects') and column references perfectly match your workbook. Also, check that there are no trailing spaces in your status cells, as "Closed " is treated differently than "Closed".
Does the COUNTIFS formula count blank cells as well?
Yes, if a cell is completely blank, the "<>Closed" criteria will still count it. To exclude blank cells from your final count, add an additional criteria pair: ,'All Defects'!I:I,"<>".
Will these exclusion formulas work in WPS Spreadsheet?
Yes, the COUNTIFS function is fully supported in WPS Spreadsheet and operates exactly as it does in Microsoft Excel, ensuring seamless format compatibility.




