Excel provides several statistical functions to help you perform tasks easily and swiftly. Today we are going to show you how to use a statistical function called: **COUNTBLANK **function in Excel. At first, you’ll get the overall use of the function within the Excel interface. Later, you’ll get the basics and five practical examples of it.

**Excel COUNTBLANK Function (Quick View)**

**Table of Contents**Expand

## Introduction to Excel COUNTBLANK Function

The **COUNTBLANK **function is categorized under statistical functions in Excel. This function counts the number of empty cells in a given range of cells.

**Summary:**

Counts the number of empty cells in a specified range of cells.

**Syntax:**

`COUNTBLANK(range)`

**Arguments:**

Argument | Required/Optional | Explanation |
---|---|---|

range |
Required | The range from which to count the blank cells. |

**Versions**

Workable from* Excel 2003*.

## COUNTBLANK Function in Excel: 5 Examples

In this section of the article, we will discuss five practical examples of using the **COUNTBLANK** function in Excel. Not to mention we used the *Microsoft Excel 365* version for this article; however, you can use any version beginning with *Excel 2003*.

### 1. COUNTBLANK Function for Rows

You can use the **COUNTBLANK **function within rows, and the function will return the empty cells from the rows.

To show you examples, we have brought a dataset of several athletes and their scores in three games. Now, let’s follow the steps mentioned below.

**Steps:**

To see how many blank games there are, we need to use **COUNTBLANK **for each of the rows. Let’s start with the first row.

- Now, enter the following formula in the
**F5**cell.

`=COUNTBLANK(C5:E5)`

Here, the **C5:E5 **cell range refers to the cell reference of the first row of our table.

- After inserting the formula, press
**ENTER**.

In the first row, we have one empty cell. For this reason, it returned **1** in cell **F5**.

- After that, dragging the
**Fill Handle**will provide the number of empty cells for the rest of the rows, as shown in the following image.

### 2. COUNTBLANK Function for Columns

The **COUNTBLANK **function can be used for the *columns* as well, and then the function will provide the empty cells from the columns.

Our sample dataset contains three companies’ year-by-year share increases over the last few years. Now, let’s use the instructions outlined below.

**Steps:**

- To see the blank cells in the columns, write the column cell reference within the
**COUNTBLANK**function. For the first column of our table, insert the below formula in**C12**cell and press**ENTER**.

`=COUNTBLANK(C5:C10)`

Here, the range **C5:C10 **indicates the cells of the column named *Company 1*.

As a result, we have found the number of empty cells in the column.

- Now, drag the
**Fill Handle**to return the blank cells for the other columns as well.

### 3. COUNTBLANK Function for the Entire Dataset

Not only for rows or columns, but we can also use the **COUNTBLANK **function for the entire dataset.

Let’s see an example using the scorer dataset.

**Steps:**

- Here, we will find the total number of empty cells in our dataset and we are going to use the below formula in the
**C12**cell.

`=COUNTBLANK(C5:E10)`

Here, the range **C5:E10 **indicates the entire dataset as the ** range** argument.

- Following that, hit
**ENTER**.

This will provide the empty cells from the range we have selected. In this case, it returns to 7, as demonstrated in the following picture.

### 4. COUNTBLANK Function with Excel Table

In this section of the article, we will learn to use the *Table format* to count blank cells by using the **COUNTBLANK **function in Excel. Let’s follow the steps outlined below.

**Steps:**

- Firstly, select your dataset and press the keyboard shortcut
**CTRL + T**.

- Following that, from the
**Create Table**dialogue box, make sure to check the box**My table has headers**option. - Then, click
**OK**.

As a result, the dataset will be converted into a table as shown in the following image.

- Now, enter the following formula in cell
**C13**.

`=COUNTBLANK(Table1[Match 1])`

Here, *Table1* is the named range of the dataset, and *Match 1* is the named range of the 2nd column of the dataset.

- Next, press
**ENTER**.

Subsequently, you will have the count of blank cells in the column named *Match 1*.

- After that, to find the total number of blank cells in the entire dataset, use the following formula in cell
**C14**.

`=COUNTBLANK(Table1[#All])`

Hare, **[#All] **indicates that the entire dataset is used as the ** range **argument of the

**COUNTBLANK**function.

- Then, hit
**ENTER**.

Consequently, you will have the count of the blank cells of the entire dataset in cell **C14 **as demonstrated in the following picture.

### 5. COUNTBLANK Function with Condition in Excel

Counting blank cells with conditions provides us with a tailored option for counting cells that meet specific criteria. In Excel, we can count blank cells with conditions by following some simple steps. These steps are discussed in the following section.

**Steps:**

- Firstly, use the formula given below in cell
**F5**.

`=IF(COUNTBLANK(C5:E5)=0,"Scored in All",COUNTBLANK(C5:E5))`

Here, the range of cells **C5:E5** indicates the cells of the 3 columns named *Match 1*,* Match 2*, and *Match 3* respectively.

**Formula Breakdown**

- Here,
**COUNTBLANK(C5:E5)**→ It returns the count of blank cells within the range**C5:E5**.**C5:E5**→ It is theargument.*range***Output**→**1**.

**IF(COUNTBLANK(C5:E5)=0,”Scored in All”,COUNTBLANK(C5:E5))**→ It becomes**IF(1=0,”Scored in All”,1)**.- Here, the
**IF function**returns**Scored in All**if the output of the**COUNTBLANK(C5:E5)**function is equal to 0. Otherwise, it will return the output of the**COUNTBLANK(C5:E5)**function. **Output**→**1**.

- Here, the

- Now, press
**ENTER**.

As a result, you will have the count of blank cells for *Ronaldo* in cell **F5 **on your worksheet.

- Finally, use the
**AutoFill**option in Excel to get the remaining outputs as shown in the image below.

You can also use various methods mentioned in this article to count blank cells with conditions in Excel.

*Note:** Here, Neymar scored in all 3 matches. That’s why there are no blank cells in the range C7:E7. For this reason, the formula returns “Scored in All” in cell F7.*

**Read More:** COUNTBLANK Not Working in Excel

## Alternative to COUNTBLANK: Excel COUNTIF Function

**The COUNTIF function** is one of the most popular alternatives to the **COUNTBALNK **function to count the non-blank cells. Now, let’s follow the instructions outlined below.

**Steps:**

- Firstly, apply the following formula in cell
**C12**.

`=COUNTIF(C5:E10, "="&"")`

Here, the range of cells** C5:E10 **indicates the cells of the columns named *Match 1*, *Match 2*, and *Match 3*.

- After that, press
**ENTER**.

Consequently, you will see the count of blank cells in cell **C12 **as demonstrated in the image below.

## How to Count Non-Blank Cells in Excel

In Excel, we can count non-blank cells by following some simple steps. These steps are discussed in detail in the following section.

**Steps:**

- Firstly, use the following formula in cell
**C12**.

`=COUNTIF(C5:E10, "<>"&"")`

Here, the range of cells **C5:E10** represents the cells of the columns named *Match 1*, *Match 2*, and *Match 3*.

- Following that, press
**ENTER**.

As a result, you will have the count of the non-blank cells within the range **C5:E10**, by using the **COUNTIF **function.

By following any of the methods that are described in this article, you can count non-blank cells in Excel quite easily.

## Things You Should Remember While Using COUNTBLANK Function in Excel

So far, we have provided numbers in our function, but it’s not necessary to set only numbers; you can provide any value, whether numbers or strings. In our range, we have numbers, and our range has 6 cells; 5 of them have numbers, and one is blank.

Here, we used the following formula in cell **C12**.

`=COUNTBLANK(C5:C10)`

The formula returned the number of empty cells as marked in the following image.

If the blank cell contains a space, then it will no longer be empty. So, we inserted a space in cell **C21**. Now, let’s use the following formula in cell **C24**.

`=COUNTBLANK(C17:C22)`

Now, the **COUNTBLANK **function will consider that cell to be non-empty, and it returned **0 **after inserting a space in cell **C21**.

If a cell contains a formula that returns *“” (empty string)*, it will be counted as a blank cell. We have set an **IF** formula that produces an empty string as a result. The formula is applied in cell **I21**.

`=IF(1>2,"Yes","")`

If we use **COUNTBLANK **for this cell, then the function will count it as blank. Now, let’s apply the following formula in cell **I24**.

`=COUNTBLANK(I17:I22)`

Here we have set a range of two cells within the function, where one has the if statement and the other one is empty. But our function will return 1, as it counts an empty string (“”) as blank.

## Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

**Download Practice Workbook**

You are welcome to download the practice workbook from the link below.

## Conclusion

That’s all for today. We have tried showing you how you can use the **COUNTBLANK **function in Excel. You can use the function to count the empty cells from rows, columns, or the entire dataset. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your **COUNTBLANK **function-related scenarios where you are stuck; we are ready to help.

**<< Go Back to Excel Functions | Learn Excel**