Skip to content

Excel function · Statistics

Excel COUNTBLANK function

COUNTBLANK returns the number of empty cells in a range: how many orders have no delivery date, how many fields of a record were left empty. It is the quickest way to find missing data.

In Turkish Excel: BOŞLUKSAY

What it does

COUNTBLANK takes a single range and counts the cells that are empty. A cell containing 0 is not empty and is not counted. Formulas that return empty text, such as ="", count as empty; since COUNTA counts them as non-empty, the two results can add up to more than the number of cells.

In data checks it is used row by row: =COUNTBLANK(B2:F2) gives the number of missing fields in each record, and combined with IF, =IF(COUNTBLANK(B2:F2)>0,"Incomplete","OK") flags the incomplete ones.

A cell holding only a space looks empty but is not, so COUNTBLANK does not count it. To test whether a single cell is empty, use ISBLANK.

Syntax

English Excel=COUNTBLANK(range)
Turkish Excel=BOŞLUKSAY(aralık)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
rangeRequiredThe range whose empty cells are counted. It takes only one range; for several ranges, add the results.

Examples

1Count orders without a delivery date

Orders not yet delivered have an empty date cell. E2 counts those empty cells.

E2fx=COUNTBLANK(C2:C7)
Count orders without a delivery date: example table
ABCDE
1OrderCustomerDeliveredNo Date
2S-1001Brown & Sons3-Oct-20253
3S-1002Northgate Ltd
4S-1003Harbour Trade7-Oct-2025
5S-1004Star Market
6S-1005Brown & Sons
7S-1006Oak Builders10-Oct-2025

3 of the six orders have no delivery date.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Count the missing fields of each record

Column E counts how many fields are empty in each customer record; column F flags the records with at least one empty field.

E2fx=COUNTBLANK(A2:D2)
Count the missing fields of each record: example table
ABCDEF
1CompanyContactEmailTownMissingStatus
2Brown & SonsEmma Brownsales@brownandsons.co.ukLeeds0OK
3Northgate Ltdaccounts@northgate.comYork1Incomplete
4Harbour TradeBristol2Incomplete
5Star MarketAva Thompsonsupport@starmarket.comHull0OK
  • E2 (filled down to E5)=COUNTBLANK(A2:D2)
  • F2 (filled down to F5)=IF(E2>0,"Incomplete","OK")

Northgate Ltd has 1 empty field and Harbour Trade 2; both are flagged Incomplete.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Zero versus an empty cell

In the stock list, sold-out products have 0 and products not yet counted are empty. COUNTBLANK counts only the empty ones; COUNTIF counts the zeros separately.

D2fx=COUNTBLANK(B2:B7)
Zero versus an empty cell: example table
ABCDE
1ProductStockNot CountedSold Out
2Grounded socket25022
3Circuit breaker0
4Light switch
5Cable duct0
6Copper cable
7Lamp holder75
  • D2=COUNTBLANK(B2:B7)
  • E2=COUNTIF(B2:B7,0)

2 products have not been counted and 2 are sold out. Zeros are not treated as blank, so the two cases stay apart.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Count too low

Cause: Some cells that look empty contain a space, so they are not counted as empty.

Fix: Clear those cells, or count with =SUMPRODUCT(--(TRIM(B2:B20)="")).

Zeros are not counted

Cause: A cell containing 0 is not empty.

Fix: To treat zeros as missing too, add two counts: =COUNTBLANK(B2:B20)+COUNTIF(B2:B20,0).

Good to know

  • For blanks in several separate ranges, add the results: =COUNTBLANK(B2:B20)+COUNTBLANK(D2:D20).
  • To find records with missing fields, add a COUNTBLANK column and filter for values above 0.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a Missing Fields column with the number of empty fields in each customer record, and flag the incomplete ones

Convert to table

Bring your customer list into the tool and type this. Nexell builds both columns with real Excel formulas and the preview shows which records are incomplete; in the downloaded file, the flags disappear as you fill in the gaps.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I count blank cells in Excel?

Use =COUNTBLANK(B2:B20). =COUNTIF(B2:B20,"") gives the same result; both treat formulas returning empty text as blank.

Why does COUNTBLANK not count a cell that looks empty?

The cell most likely contains a space. If =LEN(B5) returns more than 0, the cell is not empty; delete its contents.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free