How to Apply COUNTIF 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.

We have taken a simple dataset of the electricity bill of a Store in the 1st 6 six months of 2021.

Dats set to apply COUNTIF not equal to text or blank


Download Practice Workbook

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


5 Uses of COUNTIF to Count Not Equal to Text or Blank in Excel

We will discuss 5 methods to apply the COUNTIF function. The purpose of the COUNTIF function is to count cells with a given condition.

  • Syntax:

=COUNTIF (range, criteria)

  • Arguments:

range – The range of cells to count.

criteria – The criteria that control which cells should be counted.

Now, we will add a column named Result in the data set to show the result.

Modify data set to show result


1. COUNTIF to Count Not Equal to Blank Cells

In this section, we will which cells are not equal to blank cells. Different formulas are used for this. But we will use the universal formula here.

Step 1:

  • Go to Cell D5.
  • Then type the COUNTIF.
  • Select the range B5 to C10 and give a condition.
  • Set a condition Not equal (<>) in the 2nd argument. So, the formula becomes:
=COUNTIF(B5:C10,"<>")

Write COUNTIF function not equal to blank cells

Step 2:

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

Note:

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


2. Excel COUNTIF to Count Cells That Do Not Contain Text

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

Step 1:

  • Go to Cell D5.
  • Then type the COUNTIF.
  • Select the range B5 to C10 and give a condition.
  • In the 2nd argument write “ <>* ” and set this condition. So, the formula becomes:
=COUNTIF(B5:C10,"<>*")

Apply COUNTIF function not equal to text

Step 2:

  • Now, 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. COUNTIF 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.

Step 1:

  • Go to Cell D5.
  • Then type the COUNTIF.
  • Select the range B5 to C10.
  • 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")

Use COUNTIF not equal to specific text

Step 2:

  • Now, press ENTER.

The result is showing 11. 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.


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

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

Step 1:

  • Go to Cell D5.
  • Then type the COUNTIF.
  • Select the range B5 to C10.
  • 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.
  • Select B5 to C10 as range and subtract from the COUNTIF So, the formula becomes:
=COUNTIF(B5:C10,"<>Feb")-COUNTBLANK(B5:C10)

Combine COUNTBLANK with COUNTIF to count cells

Step 2:

  • Now, press ENTER.

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


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

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

Step 1:

  • Go to Cell D5.
  • Then type the COUNTIF.
  • Select the range B5 to C10 and give a condition.
  • In the 2nd argument write “ <>* ”.
  • Now, subtract the COUNTBLANK function from this. For COUNTBLANK select the range B5 to So, the formula becomes:
=COUNTIF(B5:C10,"<>*")-COUNTBLANK(B5:C10)

COUNTBLANK with COUNTIF to count cells not equal to text or blank

Step 2:

  • Now, press ENTER.

In this section, we get our desired result. In this output, it counts that contains only numeric values. It did not recognize the cells that contains text and was also blank.


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. If you have any suggestions or feedback, please let us know in the comment section.


Related Contents

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo