The COUNTBLANK function counts the number of empty cells in a specified range.
You can use this function on large data tables where it is difficult to look through every row.
Contents
Similar Functions
COUNT – Count the numeric values in a data set
COUNTA – Count the non-blank values in a data set
COUNTIF – Count the cells that match a criterion
COUNTIFS – Count the cells that match multiple criteria
COUNTUNIQUE – Count the unique values
COUNTUNIQUEIFS – Count the unique values that meet multiple criteria
COUNTBLANK – Count the blank cells
Insert Math Symbols (Add-On)
Syntax
=COUNTBLANK(value1,[value2,...])
value – The value or range to count for empty cells. You can optionally specify additional values.
Video Tutorial
Examples
Example 1 – Zero vs. Blank
Let’s look at the treatment of zeroes and blanks.
Formula used: =COUNTBLANK(A1:A3)
The COUNTBLANK function, using A1:A3 as the range, counts cell A2 as a blank. However, the function does not count 0 in cell A3 as a blank. Thus, the result of the function is 1.
To understand this further, let’s look at other examples.
Example 2 – Empty String Output
Cells can be empty, but they can also appear empty due to invisible characters.
Formula used: =COUNTBLANK(A1:A3)
You can easily see that cell A1 is not empty, but A2 and A3 are more complex. Cell A2 has a formula, =" ", which produces a space. Its output is the same as a user pressing the space bar. That’s just harder to show in a picture! For A3, however, the formula ="" creates a blank cell since there is nothing between the two parentheses. This blank cell in A3 produces the output of 1 in the formula.
Furthermore, numbers and text aren’t the only data types. Let’s look at some others.
Example 3 – Treatment of Other Data Types
Lastly, we’ll throw the rest of the data types into Google Sheets and see how they do.
Formula used: =COUNTBLANK(A1:A6)
The COUNTBLANK function correctly analyzes this column of boolean, error, array, and other data types. Surprisingly, Google Sheets counted the spillover in cell A4 as not blank. Even though you see data in that cell, it comes from the array {20;21} in cell A3.
Live Examples in a Linked Google Sheet
Before you go, try these examples by making a copy of the Google Sheet.