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.

## Download Practice Workbook

You can download the practice workbook from here.

## 4 Fruitful Ways to Count Empty Cells in Excel

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

The dataset contains the name 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 the row is fully blank or not.

The formula will be:

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

Follow the picture below to know 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 to count 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.

**Read more:** **How to Count Blank Cells in Excel with Condition **

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

**Read more: How to Count Filled Cells in Excel **

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

**Read more:** **Count Cells that Contain Specific Text in Excel**

### 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 alright 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 row and column of your datasets.

## 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 for you. If you have any further query you can ask in the comment section. Also don’t forget to visit **our site** for more informative articles.

## Related Readings

**How to Count Duplicate Values Only Once in Excel****Count Specific Characters in a Column in Excel: 4 Methods****How to Count Unique Names in Excel (5 Methods)****Excel Count Cells with Numbers (5 Simple Ways)****Count Cells That Are Not Blank in Excel (6 Useful Methods)****How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)**