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

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