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.

**Read More: **Excel COUNTIF to Count Cells Greater Than 1

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

**Read More:** COUNTIF Between Two Cell Values in Excel

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

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

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

**Read More: **How to Compare Two Columns Using COUNTIF Function

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

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

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

- Hit
**Enter**.

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

## Practice Section

We have provided a **Practice** section like the one below on each sheet on the right side.

**Download the Practice Workbook**

## Related Articles

- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Use Excel COUNTIF Between Time Range
- How to Calculate Frequency Using COUNTIF Function in Excel

**<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel**