Count Blank Cells with Excel COUNTIF Function: 2 Examples

Get FREE Advanced Excel Exercises with Solutions!

While working with a large number of data, you can find several bank cells in your worksheet. As a result, it sometimes gives us different results or misinterprets our dataset. Therefore, we should be aware of the blank cells in our dataset. Now, excel gives us various ways to find blank cells. In this tutorial, we are going to show how to count blank cells using the COUNTIF function in Excel.


Download Practice Workbook

You can download the following practice workbook and practice yourself.


How Does the COUNTIF Function Work in Excel?

The COUNTIF function count cells based on criteria. It basically returns the count of cells that meets the condition or criteria.

The Basic Syntax of COUNTIF Function:

=COUNTIF(range, Criteria)

To demonstrate this, we have a simple dataset of some fruits. We can use the COUNTIF function to count how many Apples are present in our dataset.

How does the COUNTIF function work?

Step 1:

First, type the following formula in Cell D5.

=COUNTIF(B5:B11,"Apple")

How does the COUNTIF function work?

Step 2:

Then, press Enter.

How does the COUNTIF function work?

As you can see, there are three Apples in our dataset.


Count Blank Cells Using the COUNTIF Function in Excel

In order to count the blank cells, we can also use the COUNTIF function in Excel. Similar to the previous example, we are using the same formula. But this time, we are changing our criteria.

We will see two examples of the use of this function for blank cells in Excel.

Example 1: Use the COUNTIF Function to Find Cells Not Containing Text

Now, in this example, we are going to show you how you can use the COUNTIF function to find blank cells that do not contain any text (not numbers).  Remember, this formula only counts cells that have no text in them. Even if your cell has a space and apparently it’s blank, but actually it is not. It has a “space.” So this formula will not count it as a blank cell.

This formula will come in handy if you are working with text values in the worksheet. Maybe, you want to know whether you have entered different types of values in your cells mistakenly or you want to count only the cells that have no text. Both ways, it will be helpful.

To demonstrate this, we have to use  Wildcard Characters.

The Basic Formula We Using:

=COUNTIF(range,”<>”&”*”)

Now, “<>” this sign means “not equal to” and asterisk (*) means the sequence of text in that range. So, our formula will count cells that have no text in them.

For the purpose of counting cells that do not have any text, we are using this dataset :

Sample Data set

Here, we have a dataset with a single column. In this column, we have some names, a blank, and a number in it. In this case, we are going to count the cells that do not contain any text.

Step 1:

Firstly, type the following formula in Cell E4:

=COUNTIF(B5:B9,"<>"&"*")

COUNTIF function for non-text cells

Step 2:

Then, press Enter. After that, you will see the result.

result of COUNTIF for non-text cells

But, look closely. It is giving us 2 even if there is only one blank cell. Basically, this formula will also count non-text cells as blank cells.

Read More: COUNTIF between Two Cell Values in Excel (5 Examples)


Similar Readings


Example 2: Use the COUNTIF Function to Find Blank Cells (All Types of Values)

Unlike the previous method, this formula will return the count of empty cells of all value types. It will count the cells that have no data in them. So, if your goal is to count all empty cells in a given range then you can definitely use this formula.

The Basic Syntax:

=COUNTIF(range,””)

Now, we are going to use the following data set which has three columns of different value types for the ease of demonstration:

Sample dataset

Here, we are going to count all the empty cells in the entire dataset.

Step 1:

First, type the following formula in Cell F5:

=COUNTIF(B5:D10,"")

COUNTIF function in excel

Step 2:

Next, press Enter.

result after using COUNTIF for blank cells

As you can see, we have successfully counted all the empty cells in our given dataset.

Read More: COUNTIF Excel Example (22 Examples)


Conclusion

To conclude, I hope these formulas will definitely help you count blank cells in excel. Download the practice workbook and try these on your own. Surely, your knowledge of Excel will increase. Also, don’t forget to check our website Exceldemy.com for various Excel-related articles and write down in the comment section if you have any confusion regarding this topic.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo