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
=COUNTBLANK(range)=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
| Argument | Required | Description |
|---|---|---|
range | Required | The 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.
=COUNTBLANK(C2:C7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Customer | Delivered | No Date | |
| 2 | S-1001 | Brown & Sons | 3-Oct-2025 | 3 | |
| 3 | S-1002 | Northgate Ltd | |||
| 4 | S-1003 | Harbour Trade | 7-Oct-2025 | ||
| 5 | S-1004 | Star Market | |||
| 6 | S-1005 | Brown & Sons | |||
| 7 | S-1006 | Oak Builders | 10-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.
=COUNTBLANK(A2:D2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Company | Contact | Town | Missing | Status | |
| 2 | Brown & Sons | Emma Brown | sales@brownandsons.co.uk | Leeds | 0 | OK |
| 3 | Northgate Ltd | accounts@northgate.com | York | 1 | Incomplete | |
| 4 | Harbour Trade | Bristol | 2 | Incomplete | ||
| 5 | Star Market | Ava Thompson | support@starmarket.com | Hull | 0 | OK |
- 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.
=COUNTBLANK(B2:B7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Stock | Not Counted | Sold Out | |
| 2 | Grounded socket | 250 | 2 | 2 | |
| 3 | Circuit breaker | 0 | |||
| 4 | Light switch | ||||
| 5 | Cable duct | 0 | |||
| 6 | Copper cable | ||||
| 7 | Lamp holder | 75 |
- 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
Add a Missing Fields column with the number of empty fields in each customer record, and flag the incomplete ones
Convert to tableBring 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.