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

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

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

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

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

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

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

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

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,

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.

Related Articles

• How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)
• Excel Formula to Count Cells with Text (Download Free Workbook)
• Excel Count Cells with Numbers (5 Simple Ways)
• Count Empty Cells in Excel (4 Ways)
• Excel Count Number of Cells in Range (6 Easy Ways)