Count Blank Cells with Excel COUNTIF Function: 2 Examples

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.

Step 1:

First, type the following formula in Cell D5.

Step 2:

Then, press Enter.

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 :

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:

Step 2:

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

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

• How to Use COUNTIF with WEEKDAY in Excel
• COUNTIF Date Is within 7 Days
• How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
• How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)
• How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)

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:

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

Step 1:

First, type the following formula in Cell F5:

Step 2:

Next, press Enter.

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

• How to Use COUNTIF to Count Cells Greater Than 0 in Excel
• COUNTIF Multiple Ranges Same Criteria in Excel
• Compare Two Tables and Highlight Differences in Excel (4 Methods)
• Excel COUNTIFS Not Working (7 Causes with Solutions)
• COUNTIF vs COUNTIFS in Excel (4 Examples)
• How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)