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 Excel interface. Later, you’ll get the basics and five practical examples of it.

**Excel COUNTBLANK Function (Quick View)**

**Table of Contents**hide

## Download Practice Workbook

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

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

## 5 Examples of Using the COUNTBLANK Function in Excel

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 of**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 meets 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 condition 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.*

**Similar Readings**

**How to Use LINEST Function in Excel (4 Suitable Examples)****Use CORREL Function in Excel (3 Examples and VBA)****How to Use MEDIAN Function in Excel (4 Suitable Examples)****Use MAX Function in Excel (6 Examples)****How to Use PROB Function in Excel (3 Examples)**

## 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)`

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

## 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 have stuck, we are ready to help. You can also have a look at our other useful articles on Excel functions and formulas on our website, **ExcelDemy**.