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.

**Table of Contents**hide

## 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.

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.

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**.

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**

**COUNTIF vs COUNTIFS in Excel (4 Examples)****Use COUNTIF with SUBTOTAL in Excel (2 Methods)****How to Use COUNTIF with WEEKDAY in Excel (2 Easy Methods)****COUNTIF Date Is within 7 Days****How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**

### 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**.

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.

**Read More: ****How to Use COUNTIF Between Two Numbers (4 Methods)**

**Similar Readings**

**Use COUNTIF with Wildcard in Excel (7 Easy Ways)****How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)****COUNTIF Between Two Dates in Excel (4 Suitable Examples)****Apply COUNTIF Function in Multiple Ranges for Same Criteria****How to Apply COUNTIF Between Two Cell Values in Excel**

## 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.

**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**.

**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.

## 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

**[Fixed] COUNTIF Function with Wildcard Not Working in Excel****How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)****Apply Excel COUNTIF with Pivot Table Calculated Field****Difference Between SUMIF and COUNTIF Functions in Excel****How to Compare Two Columns Using COUNTIF Function (4 Ways)****Use COUNTIF Function in Excel Greater Than Percentage****How to Use COUNTIF to Count Date Less Than Today in Excel**