COUNTIF Function to Count Cells That Are Not Equal to Zero

Let’s 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. We’ll use the COUNTIF function to count cells that are not equal to zero.

Method 1 – Counting with Blank Cells

There are two blank cells and two cells having zero values in the Sales column. We’ll count the blank cells as having non-zero values.

Steps:

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.

Press Enter.

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

Method 2 – Counting Without Blank Cells

Steps:

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 2. After that, we subtract this output from the first output. Therefore, the final result is 6.

Hit Enter.

Method 3 – Counting Cells with Non-Zero Number Values

Steps:

Select D16 and insert 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.

Hit Enter.

Method 4 – Using SUMPRODUCT and ISNUMBER Functions to Count Cells with Values

Steps:

Go to cell D16 and paste the following formula into it:

=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. We used the double hyphen to convert it to 1 and 0.
The SUMPRODUCT function returns the sum of the products of the corresponding values from the two arrays.

Hit Enter.

Method 5 – Using the COUNTIF Function to Count Cells That Are Not Equal to Text

Steps:

Select cell D16 and insert the following formula into the Formula Bar.

=COUNTA(B5:D14)+COUNTBLANK(B5:D14)-COUNTIF(B5:D14,"*")

The COUNTA function counts all the visible cells in the B5:D14 range. The COUNTBLANK function counts the blank cells. We added those values up. The COUNTIF function counts all the cells with a text string of any length with the help of the asterisk (*) symbol. Subtracting this from the previous calculation gets the cells without any text values.

Hit Enter.

Method 6 – Using the COUNTIF Function to Count Cells That Are Not Equal to Blank

Steps:

Go to cell D16 and enter the formula below.

=COUNTIF(B5:D14,"<>")

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