How to Use COUNTIF for Cells Not Equal to Text or Blank in Excel – 5 Examples

This is a sample dataset of the electricity bill of a Store in the 1st 6 six months of 2021.

The column Result was added to the dataset.

Method 1 – Using the COUNTIF Function to Count Cells Not Equal to Blank 

Steps:

• Select E5.
• Enter the COUNTIF.
• Select the range B5:C10 and provide a condition.
• Set the condition Not equal (<>) in the 2nd argument. This is the formula.

• Press ENTER.

This is the output. There are 2 blank cells and 10 cells are non-zero.

Method 2 – Applying the COUNTIF Function to Count Cells That Do Not Contain Text

Steps:

• Choose E5.
• Enter the COUNTIF.
• Select the range B5:C10 and provide a condition.
• In the 2nd argument enter “ <>* ” and set this condition. The formula is:

• Press ENTER.

The output displays the total count of cells that don’t have text value and the number of empty and numeric cells.

Method 3 – Utilizing the COUNTIF Function for Cells Not Equal to a Specific Text in Excel

Steps:

• Enter the COUNTIF in E5
• Select the range B5:C10.
• In the 2nd argument enter “ <>Jan ”. Cells that don’t contain “Jan” will be counted and this condition will be set. The formula is:

• Press ENTER.

11 is the output. Only 1 cell contains Jan. There are 11 cells that don’t have the text “Jan” will be counted and this condition will be set. The formula is:

Similar Readings

• COUNTIF Between Two Values with Multiple Criteria in Excel
• How to Use COUNTIF Between Two Dates and Matching Criteria in Excel

Method 4 – Combining the COUNTBLANK Function with the COUNTIF Function to Count Cells Not Equal to a Specific Text or Blank

Steps:

• Select E5.
• Enter the COUNTIF.
• Select the range B5:C10.
• In the 2nd argument enter “ <>Feb ”. Cells that don’t contain “Feb” will be counted and this condition will be set.
• Enter the COUNTBLANK function.
• Select the range B5:C10 and subtract it from the COUNTIF. The formula is:

• Press ENTER.

Blank cells are not counted. Non-zero cells are displayed, excluding the cells containing ‘Feb‘.

Method 5 – Applying the COUNTIF Function to Count Cells Not Equal to Text or Blank

Steps:

• Choose E5.
• Enter the COUNTIF.
• Select the range B5:C10 and provide a condition.
• In the 2nd argument write “ <>* ”.
• Subtract the COUNTBLANK function. For the COUNTBLANK function choose the range B5:C10. This is the formula:

• Press ENTER.

This is the output. Only numeric values are counted.

Practice Section

Practice here.

Download Practice Workbook

Download this practice workbook to exercise.

Related Articles

• Excel COUNTIF Function with Multiple Criteria & Date Range
• How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
• COUNTIF with Multiple Criteria in Different Columns in Excel
  
• How to Use COUNTIF Function Across Multiple Sheets in Excel
• How to Apply SUM and COUNTIF for Multiple Criteria in Excel
• How to Apply COUNTIF with Multiple OR Criteria in Excel
• SUMPRODUCT and COUNTIF Functions with Multiple Criteria

<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel