Many times, the database might contain empty cells. One would like to count the empty cells. Microsoft Excel has some amazing formulas and tools to do it easily for you. The article will include four different ways to count empty cells in Excel.

## How to Count Empty Cells in Excel: 4 Suitable Ways

We will use the following dataset to explain the ways to count empty cells in Excel.

The dataset contains the names of tech products and the number of sales that occurred in a technology-based company. You can notice the dataset has some empty cells. We will be counting the empty cells using formulas and tools available in Excel.

### 1. Count Empty Cells by Inserting Excel Formulas with COUNTIF, COUNTBLANK, SUMPRODUCT, etc. Functions

Excel has some useful formulas to count the blank cells in a dataset. Functions like **COUNTBLANK, COUNTIF, SUM, SUMPRODUCT,** and so forth are used to form such formulas. Let us see the formulas one by one.

#### i. Inserting COUNTBLANK to Count Empty Cells

The **COUNTBLANK** function itself explains what it can do. It can count blanks or empty cells in a row for a given range of data.

The formula for the given dataset:

`=COUNTBLANK(B5:C5)`

Using the **Fill handle** we can find out the result for the rest of the rows in the dataset.

Drag the **plus (+) **sign at the right bottom of the cell (**B5**).

See the result in the picture below.

**Formula Description:**

The formula syntax:

**=COUNTBLANK(range)**

Here, the range indicates the dataset from where you want to count the empty cells.

You can also use the nested **IF **and **COUNTBLANK **formulas to find whether the row is fully blank or not.

The formula will be:

`=IF(COUNTBLANK(B5:C5)=0,"Not Blank","Blank")`

Follow the picture below to learn how to do this.

**Formula Description:**

The syntax of the nested formula:

`=IF(logical_test,[value_if_true],[value_if_false])`

Here, **logical_test** takes the** COUNTBLANK **function and checks whether it is equal to zero or not.

**value_if_true **takes a text to display if the test is true.

**value_if_false** takes a text to display if the test is false.

#### ii. Inserting COUNTIF or COUNTIFS to Count Empty Cells

You can also use the **COUNTIF **or **COUNTIFS **function. Both will give the same result.

The formula will be:

`=COUNTIF(B5:C5,"")`

or,

`=COUNTIFS(B5:C5,"")`

Then, drag the **plus (+) **sign at the right bottom of the cell to find the count for the rest of the rows in the dataset.

The first Blank Cells column in Column D uses the **COUNTIF** function while the second one in Column E uses the **COUNTIFS** function.

You can notice that the result for both formulas is the same.

**Formula Explanation:**

The syntax of the formulas:

**=COUNTIF(range, criteria)**

**=COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2]..)**

Both the formulas take a range of the dataset and criteria based on which result will be displayed.

The **COUNTIFS** function can take multiple criteria and ranges while the **COUNTIFS** function takes only one range and criteria.

#### iii. Inserting SUM with ROWS and COLUMNS to Count Empty Cells

Moreover, there is another nested formula using **SUM**, **ROWS, **and **COLUMNS **functions, etc. to count empty rows in a dataset.

The formula is:

`=SUM(--MMULT(--(B5:C11<>""),ROW(INDIRECT("B1:B"&COLUMNS(B5:C11))))=0)`

The result shows there are two blank rows in the dataset.

*Note:** The formula counts blank if the whole row is blank. That is why it has ignored cell B8.*

**Formula Explanation:**

The individual syntax of the nested formula along with the explanations:

**=SUM(number1, [number2],..)**

The formula takes the numbers as arguments and gives the sum as a result.

**=MMULT(array1,array2)**

Here, it takes a number of arrays of the dataset.

**=ROW([reference])**

The formula with the **ROW** function takes the reference of rows in the dataset.

**=INDIRECT(ref_text,[a1])**

It takes reference text.

**=COLUMNS(array)**

The formula with the **COLUMNS** function takes an array of the dataset.

Here, the **double minus (–) **sign is used to do the forced conversion of the Boolean value TRUE or FALSE to numerical values 1 or 0.

#### iv. Inserting SUMPRODUCT to Count Empty Cells

Furthermore, **SUMPRODUCT** is also a useful formula for counting empty cells.

The formula for the given dataset will be:

`=SUMPRODUCT(--B5:C11="")`

The result shows there are 5 empty cells in the given dataset.

*Note: It counts for empty cells and not rows, unlike method c.*

**Formula Explanation:**

The syntax of the formula:

**=SUMPRODUCT(array1, [array2],..)**

Here, the function is used to take multiple arrays and provide the sum of the arrays.

In this case, we have only one set of **arrays **and the formula takes the range of the dataset only if it is equal to blank.

Then, using the **double minus (–) **sign we converted it into numerical value to get the result.

### 2. Count Empty Cells Using the Go To Special Command

On the other side, we can use **Go To Special** Command from the Home Tab to find empty cells.

Follow the steps to know how to count empty cells using **Go To Special:**

- Select the dataset.
- Select
**Go To Special**from**Find & Select**. You will find**Find & Select**from the**Editing**options present in the**Home**tab.

You can also press** F5** on your keyboard to find **Special **from there.

- A new box will appear. From the box, select
**Blanks**and click**OK.**

You will notice the blank cells are selected automatically.

- To highlight the blank cells from the
**Home**tab select**Fill Color**and choose the color you like from the drop-down menu.

The color you chose will fill the selected blank cells. Let us choose blue for now. The result will look like this.

**Note:** This process is useful for small datasets. You can highlight the blank cells and count by yourself.

### 3. Count Empty Cells Using the Find & Replace Command

Besides, you can use another useful Excel tool to count the empty cells. This is called **Find and Replace**.

You need to follow the steps to use it.

- Select the dataset.
- Select
**Find**from**Find & Select**. Follow the picture if you cannot find it.

- A new box will appear. Keep the place blank in
**Find what**: option. - Then, click on
**Options >>**.

- New options will appear. From there,
- Tick the option
**Match entire cell contents**. - From
**Within:**drop-down options, select**Sheet**. - In
**Search:**drop-down options select**By Columns.** - From
**Look in**drop-down options select**Values or Formulas.**(We will select values since we do not have any formulas in our dataset). Anyway, both will work the same.

- Tick the option

- The
**Find and Replace**box should look like the below picture. Click**Find All**and the result will be shown at the**bottom of the box.**

### 4. Count Empty Cells Using Excel VBA Macros

Lastly, the VBA Macros can be used to count empty cells.

For this, you must follow the steps:

- Select the dataset.

- Press
**ALT+F11**from the keyboard. The**VBA**window will open.

- Select the sheet where your selected dataset is present.

From **Insert **choose **Module**.

- The
**General**window will open.

- Inside the
**General**window write the code given below.

**Code:**

```
Sub CountBlanks()
'Updateby20140310
Dim rng As Range
Dim WorkRng As Range
Dim total As Long
On Error Resume Next
xTitleId = "Number of Blank Cells"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each rng In WorkRng
If IsEmpty(rng.Value) Then
total = total + 1
End If
Next
MsgBox "There are " & total & " blank cells in this range."
End Sub
```

- Press
**F5**from the keyboard to run the code. - It will open a box named “
**Number of Blank Cells**”. - Check the
**Range**of your dataset and if it is right then click**OK.**

- A new box will come up and it will show the result.

## Things to Remember

- Do not forget to select the range of data before starting to apply methods using Excel tools.
- For formulas, write the formulas maintaining the syntax of the formula, and the row and column of your datasets.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

The article explains four fruitful ways to count empty cells in Excel using various Excel formulas and tools. The formulas include functions like **COUNTBLANK, COUNTIF, SUMPRODUCT, ROWS,** and so forth. The Excel tools used in the methods are **Go To Special, Find & Replace **commands from the **Home **tab**,** and the VBA Macros to execute codes there to count empty cells in Excel. You can check the related topic in the Related Reading section. I hope this article was helpful to you. If you have any further queries, you can ask in the comment section.

**<< Go Back to Count Cells | Formula List | Learn Excel**