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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to Apply COUNTIF Not Equal to Text or Blank in Excel


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.

Result Column to Apply COUNTIF Function based on Criteria

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:
=COUNTIF(B5:C10,"<>")

COUNTIF to Count Not Equal to Blank Cells in Excel

  • 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.

Use of COUNTIF Function in Excel

Note: <> This sign means not equal. As nothing is present after this sign, it compares with blanks and returns not-blank cells.


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:
=COUNTIF(B5:C10,"<>*")

Excel COUNTIF to Count Cells That Do Not Contain Text

  • 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.

How to Apply COUNTIF Not Equal to Text or Blank in Excel


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:
=COUNTIF(B5:C10,"<>Jan")

COUNTIF Not Equal to Specific Text in Excel

  • 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.

Using COUNTIF Function for Counting Cells that Not Equal to Specific Text


Similar Readings


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:
=COUNTIF(B5:C10,"<>Feb")-COUNTBLANK(B5:C10)

Combine COUNTBLANK with COUNTIF Functions in Excel

  • 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‘.

Count Cells that Not Equal to Specific Text or Blank


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:
=COUNTIF(B5:C10,"<>*")-COUNTBLANK(B5:C10)

Use of COUNTIF & COUNTBLANK Functions

  • 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.

Pressing ENTER to COUNTIF cells that Not Equal to Text or Blank in Excel


Practice Section

Now, you can practice by yourself.

Practice Section to Apply COUNTIF Not Equal to Text or Blank in Excel


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo