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.

excel count non blank cells with condition


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.

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

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

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

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

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

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.

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

Perform the SUMPRODUCT Function to Count Non Blank Cells in Excel

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

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.

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


Similar Readings:


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.

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 cell’s 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.

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.

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.

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.

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

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

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo