Syeda Fahima Nazreen
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:
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:
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:
The formula takes the numbers as arguments and gives the sum as a result.
Here, it takes a number of arrays of the dataset.
The formula with the ROW function takes the reference of rows in the dataset.
It takes reference text.
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:
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
Similar Readings
- How to Count Cells with Specific Value in Excel (5 Easy Methods)
- Count Filtered Rows with Criteria in Excel (5 Easy Ways)
- How to Count Only Visible Cells in Excel (5 Tricks)
- Count Non Blank Cells with Condition in Excel (6 Methods)
- How to Count Number of Cells with Dates in Excel (6 Ways)
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.
- 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.
Read More: How to Count Filled Cells in Excel Using VBA (7 Methods)
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)
- Excel Formula to Count Cells with Text (All Criteria Included)
