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.
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.
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.
- At the very beginning, select cell D16 and enter the following formula.
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.
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!
- At first, go to cell D16 and insert the formula below.
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.
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.
- Firstly, move to cell D16 and write down the following formula.
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.
Thus, there are a total of six cells with Number values.
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.
- First of all, go to cell D16 and paste the following formula into the cell.
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.
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.
- Initially, select cell D16 and put down the following formula into the Formula Bar.
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 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.
- Like before, go to cell D16 and enter the formula below.
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.
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.
You may download the following Excel workbook for better understanding and practice yourself.
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.