COUNTBLANK Function
Finds the number of empty cells in a given range of cells.
What is the COUNTBLANK Function?
The COUNTBLANK function in Excel helps to find the number of empty cells in a given range of cells. The function will return an integer value acting as a count of all the empty cells in the referenced range of cells.
Financial Analysts work extensively on a large amount of data daily, which involves reconciling, reporting, analyzing, and other number-crunching tasks in Excel.
It is pretty normal that you might miss out on inserting values in specific cells in the spreadsheet. In such cases, COUNTBLANK works as a scan that lets you know if there are still any blank cells that require the input of values.
The COUNTBLANK is categorized as a statistical function that returns the count for the blank cells in the user-specified range of cells.
The function helps identify how many blank cells exist in a given range. The range could be a single column, a single row, or the entire spreadsheet. For example, if range A1:A3 are empty cells, the function would return the result for COUNTBLANK as 3.
The function will ignore cells containing text, numerical values, errors, space, date, time, and formulas. However, if the result obtained from a procedure equals an empty cell, the cell will be counted as a blank cell.
COUNTBLANK Function Formula
The syntax for the COUNTBLANK function is
=COUNTBLANK(range)
Where
range = reference to the cell or range of cells for which we need to find the count for blank cells.
The function takes in only a single argument for range. You cannot reference multiple separate rows or columns. For example, A1:A3 and C1:C3 cannot be your range for the function.
However, you can use A1:C3 as the range to count blanks, which will inevitably also include B1:B3.
How to use the COUNTBLANK Function in Excel?
The function's name says a lot about what it will do. To understand better, let us consider an example:
Example 1
Suppose that you have a column consisting of the first ten numbers. Some are represented as text, while others are described as numbers.
To find the number of blank cells in the range, we will use the formula =COUNTBLANK(C3:C12), which will give us the count as 2.
If you see closely, the cells C7 and C10 are both empty, so we get the result as 2. It doesn't matter to Excel whether the cell contains a text value or a number. It solely focuses on empty cells.
Example 2
In the previous example, the function completely ignored the cells containing text and numerical values in the result. Next, we will see other formats that may or may not be affected by the function. For example, suppose that you have the data illustrated below:
If we use the formula
=COUNTBLANK(C3:C12)
for the values in column C, we will get a result of 2.
Well, getting the result for cell C11 is entirely acceptable since it is blank, right? But what the heck is with the formula in cell C12?
We can infer another thing from this little experiment. If the formula is set up to return a blank cell, the COUNTBLANK function will include it in its final result.
'Formula A' does not return the result as an empty cell; hence, it is ignored for counting blanks.
“Not” so empty cells
When you are working on big data, there could be instances when the cells 'seem' to look empty but aren't. These are the results of automated data processing, which may or may not give rise to invisible characters in your spreadsheet.
If you use the COUNTBLANK function on such cells, you will get a misleading result since Excel considers these cells containing invisible characters to be 'non-empty.'
To avoid any errors while working on such automatically generated files or web files
- Press the keyboard shortcut of F5 to open Go To dialog box and click on ‘Special’
- Select the radio box for 'Blanks'
- Click on ‘Ok’
This method will highlight only the purely blank cells; not even a formula that gives empty cells would be included in the final result.
COUNTBLANK Function Notes
Some important things to remember are:
- The COUNTBLANK function will only process empty cells in the result while ignoring all the cells that contain text, numerical values, logical formulas, errors, etc.
- The only exception is the formulas that return empty cells as a result and will be counted as blank cells.
- Cells that contain a zero or formulas returning zero, as a result, will not be counted as blank.
- If you need to count cells containing all the types of values, use the COUNTA function.
- The COUNT function can be used when you want to count the cells containing only numerical values.
or Want to Sign up with your social account?