COUNTIF Function to Count Cells That Are Not Equal to Zero

Get FREE Advanced Excel Exercises with Solutions!

In our office and business work, we use Excel to calculate and organize a huge amount of data. Sometimes we feel the necessity to count some data with certain conditions. This article will discuss such things. Here, we’ll demonstrate how you can use the COUNTIF function in 3 cases to count cells that have values not equal to zero. So, let’s go through the article to perform them efficiently in your work.


How to Use COUNTIF Function to Count Cells That Are Not Equal to Zero: 3 Ways

For ease of understanding, we’re going to use the Sales Report of a particular grocery store. This dataset contains the names of the Sales Rep, the Product Name, and their respective Sales under columns B, C, and D correspondingly.

countif not equal to zero

Now, we’ll use the COUNTIF function to count cells that are not equal to zero. We’ll count cells in various conditions. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Counting with Blank Cells

In the dataset above, we can see that there are two blank cells and two cells having zero values in the Sales column. In this case, we opt to count the number of non-zero cells with the blank cells also. So, let’s see the process below.

📌 Steps:

  • At the very beginning, select cell D16 and enter the following formula.
=COUNTIF(D5:D14,“<>0”)

Here, D5:D14 is the range where the function works. And “<>0” is the criteria. So, the function counts the cells having non-zero values in the above range.

  • Then, press the ENTER key.

Counting with Blank Cells

There are a total of 8 cells in the Sales column, including blank cells and cells with non-zero values.

Read More: Excel COUNTIF to Count Cells Greater Than 1


2. Counting Without Blank Cells

In this case, we’ll obtain the same result as in the previous case but will exclude the blank cells from the result. So, without further delay, let’s dive in!

📌 Steps:

  • At first, go to cell D16 and insert the formula below.
=COUNTA(D5:D14)-COUNTIF(D5:D14,"=0")

Here, the COUNTA function counts all the visible cells in the D5:D14 range. It returns 8. Then, the COUNTIF function counts the number of cells having a zero value in the D5:D14 range. It returns us 2. After that, we subtract this output from the first output. Therefore, the final result is 6.

  • Later, press ENTER.

Counting Without Blank Cells

There are a total of 8 cells in the Sales column, excluding blank cells and cells with non-zero values.

Read More: COUNTIF Between Two Cell Values in Excel


3. Counting Cells with Number Values

At this time, we will count the cells with just Number values in the whole dataset, which is in the B5:D14 range. Let’s see it in action.

📌 Steps:

  • Firstly, move to cell D16 and write down the following formula.
=COUNTIF(B5:D14,"<0")+COUNTIF(B5:D14,">0")

In this formula, the COUNTIF function counts the cells with a value less than zero and a value greater than zero. So, we’ll just get the number of cells having Number values. In our dataset, there is no negative value. Thus, you can ignore the first part of the formula in this case.

  • Secondly, hit ENTER.

Counting Cells with Number Values

Thus, there are a total of six cells with Number values.

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


Using SUMPRODUCT and ISNUMBER Functions to Count Cells with Number Values

In this section, we’ll attain the same result as Case 3. But here we’ll achieve this by using the SUMPRODUCT and ISNUMBER functions. As a reminder, we used the COUNTIF function in the previous method. So, follow us until the end of the process.

📌 Steps:

  • First of all, go to cell D16 and paste the following formula into the cell.
=SUMPRODUCT(--(ISNUMBER(B5:D14)),--(B5:D14<>0))

Here, B5:D14<>0 returns an array of TRUE/FALSE. Which cells are not equal to zero return TRUE as output. Others return FALSE. Then, the double hyphen () sign converts this array to an array of 0 and 1. It transforms TRUE to 1 and FALSE to 0.
Then, the ISNUMBER function checks whether a value is a number or not in the B5:D14 range. It also returns Boolean values in an array. Then, we used the double hyphen to convert it to 1 and 0.
After that, the SUMPRODUCT function returns the sum of the products of the corresponding values from the two arrays.

  • Following this, tap the ENTER button.

Using SUMPRODUCT and ISNUMBER Functions to Count Cells with Number Values

Read More: How to Compare Two Columns Using COUNTIF Function


COUNTIF Function to Count Cells That Are Not Equal to Text

In this example, we’ll count the number of cells that have no text values. This could be a number, a zero value, or blank cells. But text values are not permissible in this counting. So, let’s begin.

📌 Steps:

  • Initially, select cell D16 and put down the following formula into the Formula Bar.
=COUNTA(B5:D14)+COUNTBLANK(B5:D14)-COUNTIF(B5:D14,"*")

Here, the COUNTA function counts all the visible cells in the B5:D14 range. Then, the COUNTBLANK function counts the blank cells. Later, we added them up. Now, the COUNTIF function counts all the cells with a text string of any length with the help of the asterisk (*) symbol. Lastly, subtract it from the previous calculation to get the cells without any text values.

  • Afterward, press the ENTER key.

COUNTIF Function to Count Cells That Are Not Equal to Text

Read More: How to Use COUNTIF Function to Calculate Percentage in Excel


COUNTIF Function to Count Cells That Are Not Equal to Blank

In our last example, we’ll get the help of the COUNTIF function again. In this case, we’ll fetch the number of cells that are not blank. There could be anything inside them without being blank. Allow me to demonstrate the process below.

📌 Steps:

  • Like before, go to cell D16 and enter the formula below.
=COUNTIF(B5:D14,"<>")

Hereabouts, the COUNTIF function counts the cells not equal to a blank string. Hither, <> sign serves as “not-equal-to”. So, the function counts cells with this criterion in the B5:D14 range and returns the output as 28.

  • As usual, press ENTER.

COUNTIF Function to Count Cells That Are Not Equal to Blank

Read More: How to Use COUNTIF Function with Array Criteria in Excel


Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it yourself.

Practice Section


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

This article explains how to use the COUNTIF function to count cells not equal to zero in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


Related Articles


<< Go Back to 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.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo