Formula Errors

Excel Formula to Count Defects That Are Not Closed or Deferred

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 868 views

Question details

The user needs an Excel formula to count records excluding two specific statuses.

Excel Formula to Count Defects That Are Not Closed or Deferred
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.
Before you start

Ensure your defect tracking data is organized in a clear column format without merged cells, and identify the exact column containing the status values.

Solution 1Recommended

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.

1
Select destination cell

Click on the empty cell where you want the final defect count to be displayed.

2
Enter the COUNTIFS formula

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.

3
Calculate the result

Press Enter to execute the formula. Excel will display the total number of defects excluding the closed and deferred ones.

Use the COUNTIFS Function for Multiple Exclusions
Syntax Note: The "<>" operator in Excel stands for 'not equal to'. This formula explicitly tells Excel: count the cell if it is not equal to Closed AND not equal to Deferred.
Efficient Data Analysis with WPS Office

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. 1. Open your workbook: Launch WPS Spreadsheet and open your defect tracking workbook.
  2. 2. Select a cell: Click on the cell where you want to display the active defect count.
  3. 3. Insert formula: Go to the Formulas tab or type directly to insert the =COUNTIFS formula with your exclusion criteria.
  4. 4. View results: Press Enter to instantly view your filtered data counts.
Fully compatible with Microsoft Excel formulas and functionsFree, lightweight, and easy to installBuilt-in data filtering and advanced chart features for defect trackingClean, tabbed interface for managing multiple project sheets
microsoft office alternative - wps office

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.