While dealing with a large **Microsoft Excel**, sometimes we need to **count non-blank cells** with several conditions. In our **Excel **worksheet for the convenience of our work, we keep some cells blank. That’s why we count those cells which are not blank. In this article, we’ll learn **six **quick and suitable ways to count non blank cells with condition in **Excel **effectively with appropriate illustrations.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 6 Suitable Ways to Count Non Blank Cells with Condition in Excel

Let’s say, we have a dataset that contains information about several **Students **of **ABC School**. The ID of the students, their securing marks in Physics and Chemistry, and their names have been given in columns **C, D, E, **and **B** respectively. We will count non-blank cells from our dataset by applying the **COUNTIF **function with different conditions. We also use the **COUNTA**, **SUMPRODUCT**, **COUNTIFS**, **TRIM**, **ROWS**, **COLUMNS**, and **COUNTBLANK **functions to count non-blank cells in Excel. Here’s an overview of the dataset for today’s task.

### 1. Apply the COUNTIF Function to Count Non Blank Cells with Condition

The **COUNTIF **function is the first and foremost function to count non-blank cells with several conditions. Let’s follow the sub-methods to count non-blank cells with several conditions.

#### 1.1 Count Non Blank Cells If Cell Value is Greater Than Another Cell

From our dataset, we will count those cells whose value is **greater **than **80 **which means the students are obtaining marks **greater **than **80**. Let’s follow the steps below.

**Steps:**

- First, select cell
**D15**to count those cells whose value is**greater**than**80**.

- Now, type
**the COUNTIF function**in the**Formula Bar.****The COUNTIF function**is,

`=COUNTIF(B6:E13, ">80")`

- Where
**B6:E13**is the cell reference and**>80**is the criteria which means the cell’s value is**greater**than**80**.

- Further, press
**Enter**on your**keyboard**and you will be able to get the return of**the COUNTIF function**and the return is**11**.

#### 1.2 Count Non Blank Cells If Cell Value is Less Than Another Cell in Excel

Now, we’ll count those cells whose value is **less **than **80**. Let’s follow the instructions below to learn!

**Step 1:**

- First, select cell
**D15**to count those cells whose value is**less**than**80**.

- Hence, type
**the COUNTIF function**in the**Formula Bar.****The COUNTIF function**is,

`=COUNTIF(B6:E13, "<80")`

- Where
**B6:E13**is the cell reference and**<80**is the criteria which means the cell’s value is**less**than**80**.

**Step 2:**

- Further, press
**Enter**on your**keyboard**and you will be able to get the return of**the COUNTIF function**and the return is**5**.

#### 1.3 Count Non Blank Cells If Cell Value is Equal to Another Cell

In this sub-method, we’ll count those cells whose value is **equal **to **80 **by applying **the COUNTIF function**. Let’s follow the steps below to learn!

**Step 1:**

- First, select cell
**D15**to count those cells whose value is**equal**to**80**.

- After that, type
**the COUNTIF function**in the**Formula Bar.****The COUNTIF function**is,

`=COUNTIF(B6:E13, "=80")`

- Where
**B6:E13**is the cell reference and**=80**is the criteria which means the cell’s value is**equal**to**80**.

**Step 2:**

- Further, simply press
**Enter**on your**keyboard**and you will be able to get the return of**the COUNTIF function**which has been given in the below screenshot and the return is**3.**

#### 1.4 Count Non Blank Cells If Cell is not Equal to Each Other

After learning the above process, we’ll count those cells whose value is **not equal to** **each other **by applying **the COUNTIF function**. Let’s follow the steps below to learn!

**Step 1:**

- First, select cell
**D15**to count those cells whose value is**not equal to each other**.

- After that, type
**the COUNTIF function**in the**Formula Bar.****The COUNTIF function**is,

`=COUNTIF(B6:E13, "<>")`

- Where
**B6:E13**is the cell reference and**<>**is the criteria which mean the cell’s value is**not equal to each other**.

**Step 2:**

- Hence, press
**Enter**on your**keyboard**and you will be able to get the return of**the COUNTIF function**and the return is**28**.

**Read More: Count Cells that Contain Specific Text in Excel**

### 2. Use the COUNTA Function to Count Non Blank Cells in Excel

In this method, we will count non-blank cells by using the **COUNTA function.** Let’s follow the instructions below to learn!

**Step 1:**

- First of all, select cell
**D15**to count non-blank cells.

- Hence, type
**the COUNTIF function**in the**Formula Bar.****The function**is,

`=COUNTA(B6:E13)`

- Where
**B6:E13**is the cell reference.

**Step 2:**

- Further, press
**Enter**on your**keyboard**and you will be able to get the return of**the COUNTA function**and the return is**29**.

**Related Content: How to Count Blank Cells in Excel with Condition (3 Methods)**

### 3. Perform the SUMPRODUCT Function to Count Non Blank Cells with Condition in Excel

You can also apply **the SUMPRODUCT function** to count non-blank cells. Undoubtedly, this is the **easiest **and most **time-saving** way to count non-blank cells. Let’s follow the instructions below to learn!

**Step 1:**

- First, select cell
**D15**.

- After selecting the cell
**D15**, type**the SUMPRODUCT function**in the**Formula Bar.****The SUMPRODUCT function**is,

`=SUMPRODUCT((B6:E13<>"")*1)`

- Where
**B6:E13**is the cell reference.

**Step 2:**

- Hence, press
**Enter**on your**keyboard**and you will be able to get the return of**the SUMPRODUCT function**and the return is**29**.

**Related Content: How to Count Blank Cells in Excel (5 Ways)**

**Similar Readings:**

**How to Count Number of Cells with Dates in Excel (6 Ways)****Count Odd and Even Numbers in Excel (3 Easy Ways)****How to Count Filled Cells in Excel Using VBA (7 Methods)**

### 4. Insert the COUNTIFS Function to Count Non Blank Cells with Condition in Excel

After learning the above methods, we’ll count non-blank cells by applying **the COUNTIFS function**. Let’s follow the steps below to learn!

**Step 1:**

- First, select cell
**D15**.

- After that, type
**the COUNTIFS function**in the**Formula Bar.****The COUNTIFS function**is,

`=COUNTIFS(B6:E13, "<>")`

- Where
**B6:E13**is the cell reference and**<>**is the criteria which mean the cell’s value is**not equal to each other.**

**Step 2:**

- Hence, press
**Enter**on your**keyboard**and you will be able to get the return of**the COUNTIFS function**and the return is**29**.

**Related Content:** **How to Count If Cell Contains Number (Easiest 7 Ways)**

### 5. Merge the SUMPRODUCT and TRIM Functions to Count Non Blank Cells with Condition in Excel

Now, we’ll apply **the SUMPRODUCT and TRIM functions** to count non-blank cells. Obviously, applying these functions to count non-blank cells is the easiest and most time-saving also. Let’s follow the steps below to learn!

**Step 1:**

- First, select cell
**D15**.

- After that, type
**the SUMPRODUCT and TRIM functions**in the**Formula Bar.****The SUMPRODUCT and TRIM functions**are,

`=SUMPRODUCT(--(TRIM(B6:E13)<>""))`

**Formula Breakdown:**

- Inside
**the TRIM function**,**B6:E13**is the cell’s reference,**<>**is the criteria which mean the cell’s value is not equal to each other, and**“”**is used that cells are not blank **The SUMPRODUCT function**counts the non-blank cells.

** **

**Step 2:**

- Hence, press
**Enter**on your**keyboard**and you will be able to get the return of**the SUMPRODUCT and TRIM functions**and the return is**28**.

**Read More: How to Count Filled Cells in Excel (5 Quick Ways)**

### 6. Combine the ROWS, COLUMNS, and COUNTBLANK Functions to Count Non Blank Cells in Excel

Last but not least, we’ll apply **the ROWS, COLUMNS, and COUNTBLANK functions** to count non-blank cells. We can easily count the non-blank cells by using these functions. Let’s follow the instructions below to learn!

**Step 1:**

- First, select cell
**D15**.

- After that, type
**the ROWS, COLUMNS, and COUNTBLANK functions**in the**Formula Bar.****The functions**are,

`=ROWS(B6:E13)*COLUMNS(B6:E13)-COUNTBLANK(B6:E13)`

**Formula Breakdown:**

- The
**ROWS**function count total**rows**and**COLUMNS**function counts the total**columns**. By multiplying these two functions we will be able to count the total cells with blank and non-blank cells. - After that, the
**COUNTBLANK**function counts the total blank cell, and then the**minus(-) sign**subtracts the blank cells from the total cells.

**Step 2:**

- Hence, press
**Enter**on your**keyboard**and you will be able to get the return of**the ROWS, COLUMNS, and COUNTBLANK functions,**and the return is**29**.

**Related Content: How to Count Only Visible Cells in Excel (5 Tricks)**

## Things to Remember

👉 **#REF!** error occurs when cell reference is not valid.

## Conclusion

I hope all of the suitable methods mentioned above to count non-blank cells will now provoke you to apply them in your **Excel** spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.