COUNTBLANK Function – Google Sheets

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.

Help text for the COUNTBLANK function

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

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.

The COUNTBLANK function not counting a zero as a blank
Counting Numbers 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.

The COUNTBLANK function not counting the output of a blank string
Counting Text Strings and Blanks

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.

The COUNTBLANK function not finding any blanks in a column of mixed data types
No Blanks to Count

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.

Leave a Comment