Practice free

COUNTBLANK function

COUNTBLANK counts the blank cells in a range, which is handy for spotting missing data. Cells holding an empty string from a formula are counted as blank too.

Example

Read it as a sentence: count the empty cells in D1:D5, which gives 5.

Practice this in the app

Syntax

COUNTBLANK(range)

range (required)
Range in which to count empty cells.

Returns: The number of empty cells in the range.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=COUNTBLANK(Data!D1:D5) 5 All blank
=COUNTBLANK(Data!B1:B8) 1 Mixed range (one blank)
=COUNTBLANK(Data!C1:C5) 0 Range with error
=COUNTBLANK(Data!D1) 1 Single blank cell
=COUNTBLANK(Data!AP1) 1 Empty string from formula counts as blank

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Needs a range; an array constant gives #VALUE!.
  • Cells with zero are not blank.

Where it works

Excel
COUNTBLANK is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .