Alok Paul
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Examples of Using COUNTIF Function to Count Cells Which Are Not Equal to Text or Blank in Excel
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:
=COUNTIF(B5:C10,"<>")
- 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.
Read More: Excel COUNTIF for Multiple Criteria with Different Column
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,"<>*")
- 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.
Read More: How to Apply COUNTIF Function in Excel with Multiple Criteria
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")
- 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.
Read More: Excel COUNTIF Function with Multiple Criteria & Date Range
Similar Readings
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- COUNTIF Between Two Values with Multiple Criteria in Excel
- How to Use COUNTIF Function Across Multiple Sheets 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:
=COUNTIF(B5:C10,"<>Feb")-COUNTBLANK(B5:C10)
- 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‘.
Read More: How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
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)
- 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.
Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
Practice Section
Now, you can practice by yourself.
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.
