AI Excel Generator
← ALL GUIDES

How to Count Blank Cells in Excel

Updated November 4, 2026 · AI Excel Generator

To count blank cells in Excel, use COUNTBLANK for a straightforward range or COUNTIF when you need more control.

The right formula depends on whether cells are truly empty, contain formulas that return an empty string, or only appear blank because of spaces or formatting.

Count blank cells with COUNTBLANK

The simplest way to count blank cells is COUNTBLANK.

Give it a range, and it returns the number of cells Excel considers blank.

=COUNTBLANK(B2:B20)

This formula counts blank cells in B2:B20.

For example, if six cells in that range are blank, the result is 6.

You can use a single-cell range, a row, a column, or a rectangular block, such as =COUNTBLANK(A1:D10).

COUNTBLANK counts cells that are empty and cells containing formulas that return an empty text string, such as =IF(A2="","",A2*2).

It does not count cells containing a space character, even if the space is hard to see.

Count blank cells with COUNTIF

For an equivalent count on a typical range, use COUNTIF with an empty-string criterion:

=COUNTIF(B2:B20,"")

This counts empty cells and usually includes formulas whose displayed result is an empty string.

The criterion "" means that the cell’s value is empty text.

Use COUNTBLANK when you simply want a clear blank-cell count; use COUNTIF when you plan to extend the formula with other conditions.

For example, to count blank cells in column C only for rows where column A contains “Open,” use:

=COUNTIFS(A2:A20,"Open",C2:C20,"")

Both ranges should cover the same rows.

This formula counts rows meeting both conditions: column A says “Open,” and the corresponding cell in column C is blank.

Count truly empty cells only

There is a difference between a cell that has never contained a value and one that contains a formula displaying nothing.

COUNTBLANK counts both.

To count only genuinely empty cells in B2:B20, use:

=SUMPRODUCT(--ISBLANK(B2:B20))

ISBLANK returns TRUE only when a cell is empty; a formula such as =IF(A2="","",A2) makes the cell nonempty even when it displays nothing.

SUMPRODUCT adds the TRUE results after the double unary operator converts them to 1s.

In Microsoft 365 and newer Excel versions, you can also use a dynamic-array formula:

=SUM(--ISBLANK(B2:B20))

For broad compatibility, including older Excel releases, SUMPRODUCT is a practical choice that does not require a special array-entry key combination.

Count cells that look blank because of spaces

A cell containing one or more spaces is not blank.

Neither COUNTBLANK nor COUNTIF(range,"") counts it as blank.

If imported data may contain spaces, count cells that are empty after trimming ordinary leading and trailing spaces:

=SUMPRODUCT(--(LEN(TRIM(B2:B20))=0))

This counts empty cells and cells containing only ordinary space characters.

TRIM removes extra standard spaces, and LEN checks whether any text remains.

Note that TRIM does not remove every invisible character; nonbreaking spaces copied from webpages may require additional cleanup, such as using SUBSTITUTE with CHAR(160).

To handle ordinary and nonbreaking spaces, try:

=SUMPRODUCT(--(LEN(TRIM(SUBSTITUTE(B2:B20,CHAR(160)," ")))=0))

This replaces nonbreaking spaces with standard spaces before trimming.

For complex imported text, clean the source data first and confirm that the formula matches the kinds of hidden characters in your data.

Count blanks in multiple ranges

You can add separate blank counts when the ranges are not adjacent:

=COUNTBLANK(B2:B20)+COUNTBLANK(D2:D20)

For a single rectangular area, include the whole block instead, for example =COUNTBLANK(B2:D20).

Be aware that counting a rectangle also includes blank cells in its middle columns.

To count blanks across noncontiguous ranges with one SUMPRODUCT formula, add separate tests:

=SUMPRODUCT(--(B2:B20=""))+SUMPRODUCT(--(D2:D20=""))

This equality test can treat formula results of "" as blank, but it does not count cells containing spaces as blank.

If you need to distinguish truly empty cells in several ranges, use ISBLANK on each range and add the results.

Common issues and checks

  • The result is lower than expected: Some cells may contain spaces, nonprinting characters, or formulas that return values other than an empty string.
  • The result includes cells with formulas: That is expected for COUNTBLANK when those formulas return "".

    Use ISBLANK if you mean cells with no content or formula.
  • The cell looks empty but is not counted: Check whether it contains a space or an invisible character.

    Select the cell and inspect the formula bar, or test it with =LEN(B2).
  • The formula shows an error: Confirm that the ranges are valid and that list separators match your regional settings.

    Some Excel locales use semicolons instead of commas, such as =COUNTIF(B2:B20;"").
  • Formatting makes a value appear missing: A custom number format, white font, or conditional formatting can hide a value without making the cell blank.

    Blank-count formulas check cell contents, not whether content is visible.

Excel and Google Sheets compatibility

COUNTBLANK, COUNTIF, COUNTIFS, ISBLANK, SUMPRODUCT, TRIM, SUBSTITUTE, and LEN are available in current versions of Excel and Google Sheets.

The examples use comma separators; regional settings may require semicolons.

Dynamic-array behavior and some function details can vary by version, so the SUMPRODUCT option is useful when you need a formula that works across a wider range of Excel releases.