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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel count non blank cells with condition


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.

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

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

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


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.

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

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

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


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.

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

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

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


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.

Count Non Blank Cells If Cell 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 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.

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


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.

Use the COUNTA Function to Count Non Blank Cells in Excel

  • Hence, type the COUNTIF function in the Formula Bar. The function is,
=COUNTA(B6:E13)
  • Where B6:E13 is the cell reference.

Use the COUNTA Function to Count Non Blank Cells in Excel

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.

Perform the SUMPRODUCT Function to Count Non Blank Cells in Excel

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

Perform the SUMPRODUCT Function to Count Non Blank Cells in Excel

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.

Insert the COUNTIFS Function to Count Non Blank Cells in Excel

  • 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 cells’ value is not equal to each other.

Insert the COUNTIFS Function to Count Non Blank Cells in Excel

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.

Merge the SUMPRODUCT and TRIM Functions to Count Non Blank Cells in Excel

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

Merge the SUMPRODUCT and TRIM Functions to Count Non Blank Cells in Excel

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.

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

  • 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 counts the total rows and the 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 cells, 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.

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo