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

In our office and business works, we use Excel to calculate and organize a huge amount of data. Sometimes we feel the necessity to count some data with some conditions. In this article, we will discuss how to apply the COUNTIF function that is not equal to text or blank.
Here, we have taken a simple dataset of the electricity bill of a Store in the 1st 6 six months of 2021.

Using COUNTIF Function to Count Cells Which Are Not Equal to Text or Blank in Excel: 5 Useful Examples

We will discuss 5 examples to apply the COUNTIF function. The purpose of the COUNTIF function is to count cells with a given condition.
Now, we will add a column named Result in the data set to show the result.

Furthermore, for this session, we’re going to use Microsoft 365 version.

1. Using COUNTIF Function to Count Cells Not Equal to Blank 

In this section, we will which cells are not equal to blank cells. Here, you may use different formulas for this. However, we will use the universal formula here.

Steps:

• Firstly, select Cell E5.
• Secondly, type the COUNTIF.
• Thirdly, select the range B5 to C10 and give a condition.
• Fourthly, set a condition Not equal (<>) in the 2nd argument. So, the formula becomes:

• Now, press ENTER. And we’ll get the result. From the data set, we easily see that we have only 2 blank cells and 10 cells are non-zero.

2. Applying COUNTIF Function to Count Cells That Do Not Contain Text

Here, we will use COUNTIF to count cells that do not contain text. Additionally, we only consider blank and numeric values here.

Steps:

• First, choose Cell E5.
• Then, type the COUNTIF.
• Subsequently, select the range B5 to C10 and give a condition.
• After that, in the 2nd argument write “ <>* ” and set this condition. So, the formula becomes:

• Then, press ENTER.

Here, we’ve got the total count of those cells that don’t have any text value. It shows the number of empty and numeric cells.

3. Utilizing COUNTIF Function for Cells Not Equal to Specific Text in Excel

In this section, we will apply the COUNTIF function to count cells that are not equal to a specific text.

Steps:

• First, in Cell E5 >> type the COUNTIF.
• Then, select the range B5 to C10.
• Finally, in the 2nd argument write “ <>Jan ”. It will now count the cells that don’t contain “Jan” and set this condition. So, the formula becomes:

• After that, press ENTER.

As a result, we got 11 as output. From the data set, we see that only 1 cell contains Jan. So, the rest are 11 cells that don’t have the text “Jan”. In this section blank cells are also counted.

Similar Readings

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

4. Combine COUNTBLANK Function with COUNTIF Function to Count Cells Not Equal to Specific Text and Blank

In this section, we’ll combine the COUNTBLANK function with the COUNTIF function. Here, blank cells will be removed by this function.

Steps:

• Firstly, select Cell E5.
• Secondly, type the COUNTIF.
• Thirdly, select the range B5 to C10.
• Fourthly, in the 2nd argument write “ <>Feb ”. It will now count the cells that don’t contain “Feb” and set this condition.
• Now, write the COUNTBLANK function.
• Then, select B5 to C10 as the range and subtract from the COUNTIF So, the formula becomes:

• Lastly, press ENTER.

Here, we’ve removed the blank cells from counting. Thus, we’ve got only the non-zero cells in the result excluding the cells containing a specific text ‘Feb‘.

5. Applying COUNTIF Function to Count Cells Not Equal to Text or Blank

This is the last method. Here, we will get our most desired output from here. Again, we will use COUNTBLANK with COUNTIF here.

Steps:

• First, choose Cell E5.
• Then, type the COUNTIF.
• After that, select the range B5 to C10 and give a condition.
• Subsequently, in the 2nd argument write “ <>* ”.
• Now, subtract the COUNTBLANK function from this. For the COUNTBLANK function choose the range B5 to C10. Thus, the formula becomes:

• Now, press ENTER.

Finally, we get our desired result. Here, in this output, it counts that contain only numeric values. Actually, it did not recognize the cells that contains text and was also blank.

Practice Section

Now, you can practice by yourself.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Here we’ve discussed five different uses of the COUNTIF function to count cells that are not equal to text or blank in different conditions. Hope this article will be quite helpful to you while applying the methods in Excel spreadsheets as well. Please let us know in the comment section if you have any suggestions or feedback.

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