How to Count Non Blank Cells with Condition in Excel (6 Methods)

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 conditions in Excel effectively with appropriate illustrations.

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. Applying 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 Counting 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,

• 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 Counting 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,

• 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 Counting 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,

• 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 Counting 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,

• Where B6:E13 is the cell reference and <> is the criteria which mean the cells’ 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.

2. Using Excel COUNTA Function to Count Non Blank Cells

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,

• 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. Using Excel SUMPRODUCT Function to Count Non Blank Cells with Condition

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 cell D15, type the SUMPRODUCT function in the Formula Bar. The SUMPRODUCT function is,

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

4. Inserting Excel COUNTIFS Function to Count Non Blank Cells with Condition

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,

• Where B6:E13 is the cell reference and <> is the criteria which mean the cells’ 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.

5. Counting Non Blank Cells with Condition by Merging SUMPRODUCT and TRIM Functions

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

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.

6. Combining 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,

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.

Things to Remember

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

Download Practice Workbook

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

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.

<< Go Back to Count Cells | Formula List | Learn Excel