COUNTIF Function to Count Cells That Are Not Equal to Zero

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.


Download Practice Workbook

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


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

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: Use Excel COUNTIF Function to Count Cells Greater Than 0


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: Excel COUNTIF with Greater Than and Less Than Criteria


Similar Readings


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: COUNTIF Excel Example (22 Examples)


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 Use COUNTIF Between Two Numbers (4 Methods)


Similar Readings


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: VBA COUNTIF Function in Excel (6 Examples)


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: Count Blank Cells with Excel COUNTIF Function: 2 Examples


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


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. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo