Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Statistical Under review

COUNTBLANK

AANTAL.LEGE.CELLEN in Dutch Excel

Counts the empty cells in one range, including cells that hold empty text from a formula, but not cells with a space or a 0.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Invoice no.	Customer	VAT code	Without VAT code
2	F-25-1001	Hoekstra Installatie BV	V21	33=COUNTBLANK(C2:C8)=COUNTBLANK(C2:C8)
3	F-25-1002	Smit Groothandel BV
4	F-25-1003	Van Dijk Transport BV	V21
5	F-25-1004	De Boer Metaal BV
6	F-25-1005	Kaya Elektro BV	V09
7	F-25-1006	Mulder Gereedschappen
8	F-25-1007	Raman Klimaattechniek	V21

Result: 3

=COUNTBLANK(C2:C8)

=COUNTBLANK(C2:C8)

What COUNTBLANK does and when to use it

What it does: it counts the empty cells in one range, for example the sales invoices that have no VAT code.

When to use it: for completeness checks on exports: gaps in a column, or the share of lines not yet counted (divide by ROWS). For empty cells under a condition use COUNTIFS.

How Excel evaluates it

Excel goes through every cell of the range once. A cell counts when it has no content at all, or when it holds empty text.

The function then returns the total as a whole number. A block such as B2:D9 is counted as one range, cell by cell.

Watch out: A cell with a space or a 0 is not counted, however empty it looks on screen. COUNTBLANK counts only cells with no content or with empty text.

Terms used on this page: range, cell

Syntax

Official COUNTBLANK documentation

COUNTBLANK(range)

COUNTBLANK(range)

Arguments
Argument	Type	Meaning
`range`	Required	The cells to count the empty ones in.

Return value:The number of empty cells in the range, as a whole number

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Count sales invoices without a VAT code

You are recalculating VAT on the sales invoices of Veldhuis Installatie BV. The extract has a VAT code column, but some invoices were booked without one. Before you start, you want one number: how many invoices have no code.

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Invoice no.	Customer	VAT code	Without VAT code
2	F-25-1001	Hoekstra Installatie BV	V21	33=COUNTBLANK(C2:C8)=COUNTBLANK(C2:C8)
3	F-25-1002	Smit Groothandel BV
4	F-25-1003	Van Dijk Transport BV	V21
5	F-25-1004	De Boer Metaal BV
6	F-25-1005	Kaya Elektro BV	V09
7	F-25-1006	Mulder Gereedschappen
8	F-25-1007	Raman Klimaattechniek	V21
9

Formula cell (E2)Input

Formula in E2

=COUNTBLANK(C2:C8)

=COUNTBLANK(C2:C8)

Result: 33

Step by step

COUNTBLANK has one argument, range. Here it is C2:C8, the VAT code of the 7 invoices.
Excel goes through the cells from top to bottom. C2 holds V21: counted nothing. C3 is empty: the count becomes 1. C4 holds V21: still 1.
C5 is empty: the count becomes 2. C6 holds V09: still 2. C7 is empty: the count becomes 3. C8 holds V21: still 3.
The result is 3: invoices F-25-1002, F-25-1004 and F-25-1006 have no VAT code. Type a code in C3 and the result becomes 2 without touching the formula.

Watch out: If the range stops one row early, for example C2:C7, the last invoice is not counted at all. Check that the range covers every data row.

Express the uncounted stock lines as a percentage

During the stock count at year end the quantity column of the inventory list (voorraadlijst) is filled in by hand. Some lines are still open. A 0 means someone counted the shelf and found nothing; an empty cell means nobody counted it yet.

You want to report what share of the lines has not been counted.

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Item	Description	Counted quantity	Not yet counted
2	S-201	Cement 25 kg	4040	37.5%37,5%=COUNTBLANK(C2:C9) / ROWS(C2:C9)=COUNTBLANK(C2:C9) / ROWS(C2:C9)
3	S-202	Plywood 18 mm
4	S-203	Wood screws 4x40	00
5	S-204	Paint white 10 L	1818
6	S-205	Safety gloves
7	S-206	Ladder 3 m	66
8	S-207	Drill bits set
9	S-208	Work lamp	2222
10

Formula cell (E2)InputNote: C3 and C8 are empty. C6 holds empty text, as left behind by a formula that returned "" and was pasted as values. C4 holds a typed 0.

Formula in E2

=COUNTBLANK(C2:C9) / ROWS(C2:C9)

=COUNTBLANK(C2:C9) / ROWS(C2:C9)

Result: 37.5%37,5%

Step by step

Excel evaluates COUNTBLANK(C2:C9) first. It goes through the 8 cells: C2 holds 40, C3 is empty (1), C4 holds 0, C5 holds 18, C6 holds empty text (2), C7 holds 6, C8 is empty (3), C9 holds 22. The count is 3.
The 0 in C4 is not counted: it is a value, and the shelf was counted. The empty text in C6 is counted, even though the cell is not truly empty.
ROWS(C2:C9) returns the number of rows in the range: 8.
The division is 3 / 8 = 0.375. Formatted as a percentage, E2 shows 37.5%: more than a third of the stock lines has not been counted.
Fill in C3 with a quantity and the result drops to 2 / 8 = 25%. Without the division by ROWS you would only see 3 or 2, a number with no reference size.

Watch out: The cell shows 0.375 until you format it as a percentage. The value is right; only the display is not.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

COUNTBLANK supplies the numerator and ROWS supplies the size of the whole range, so the fraction stays correct if rows are added inside the range. The count treats a counted 0 as filled in, which is exactly what this scenario needs.

More to watch out for

If the list gets new rows below C9, the range does not grow. Convert the list to a table, or include the new rows in the range.

Variations

=1 - COUNTBLANK(C2:C9) / ROWS(C2:C9)

=1 - COUNTBLANK(C2:C9) / ROWS(C2:C9)

The share that has been counted instead of the share still open: 62.5%.

Advanced

Find what share of the missing approvals sits on manual entries

An extract of 8 journal lines has an Approved by column with gaps. Manual entries (journal MEM) carry the highest risk, so the finding you want is not the number of gaps but the share of the gaps that sits on manual entries. COUNTBLANK gives the total; a second function must count only the MEM lines.

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Entry	Journal	Approved by	Share on MEM
2	25-0801	MEM	JK	50%50%=COUNTIFS(B2:B9, "MEM", C2:C9, "") / COUNTBLANK(C2:C9)=COUNTIFS(B2:B9; "MEM"; C2:C9; "") / COUNTBLANK(C2:C9)
3	25-0802	VRK
4	25-0803	MEM
5	25-0804	INK	MV
6	25-0805	MEM
7	25-0806	BNK	RB
8	25-0807	INK
9	25-0808	MEM
10

Formula cell (E2)InputMarks: extra spaceNote: C3, C4, C6 and C8 are empty. C9 holds a single space, which looks empty but is not.

Formula in E2

=COUNTIFS(B2:B9, "MEM", C2:C9, "") / COUNTBLANK(C2:C9)

=COUNTIFS(B2:B9; "MEM"; C2:C9; "") / COUNTBLANK(C2:C9)

Result: 50%50%

Step by step

Excel evaluates COUNTIFS first. Its first pair is B2:B9 and "MEM": the rows with journal MEM are 2, 4, 6 and 9, entries 25-0801, 25-0803, 25-0805 and 25-0808.
The second pair is C2:C9 and "", the criterion for empty cells. Of the four MEM rows, C2 holds JK, C4 is empty, C6 is empty and C9 holds a space. The space does not match the criterion, so COUNTIFS returns 2.
COUNTBLANK(C2:C9) counts the empty cells of the whole column: C3, C4, C6 and C8. The count is 4. C9 is not counted.
The division is 2 / 4 = 0.5, shown as 50%. Half of the missing approvals belong to manual entries.

Watch out: C9 holds a space on a MEM line. Neither COUNTBLANK nor the "" criterion counts it, so the formula silently reports 50% where the true picture is 3 of 5 gaps, 60%. Test the column for spaces before you quote the percentage.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

COUNTBLANK takes one range and no conditions, so it can only give the total. COUNTIFS adds the condition on the journal column, and its "" criterion matches the same kind of empty cells as COUNTBLANK. Both count the same cells, which is why the two numbers can be divided.

More to watch out for

All ranges inside COUNTIFS must be the same size. B2:B9 against C2:C8 gives #VALUE!, and the mismatch slips through when the ranges are typed by hand.

Variations

=SUMPRODUCT((B2:B9 = "MEM") * (TRIM(C2:C9) = "")) / SUMPRODUCT(--(TRIM(C2:C9) = ""))

=SUMPRODUCT((B2:B9 = "MEM") * (TRIM(C2:C9) = "")) / SUMPRODUCT(--(TRIM(C2:C9) = ""))

Counts every cell that looks empty, so the space in C9 is included: 3 of 5, which is 60%. Choose this when the extract may contain spaces.

=COUNTIFS(B2:B9, "MEM", C2:C9, "")

=COUNTIFS(B2:B9; "MEM"; C2:C9; "")

Only the number of MEM lines without approver, 2, when the share is not needed.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Measure how far COUNTBLANK overstates completeness

The purchase invoice extract has a cost center column. COUNTBLANK reports 2 gaps in 8 lines, so the field looks 75% complete. Some cells hold only a space, though.

As the reviewer you want to state by how many percentage points the plain count overstates completeness, in one formula that others can follow.

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Invoice no.	Supplier	Cost center	Overstatement
2	I-25-2201	Bakker Isolatie BV	KP100	25%25%=LET( field, C2:C9, n, ROWS(field), strictGaps, COUNTBLANK(field), visibleGaps, SUMPRODUCT(--(TRIM(field) = "")), (visibleGaps - strictGaps) / n )=LET( field; C2:C9; n; ROWS(field); strictGaps; COUNTBLANK(field); visibleGaps; SUMPRODUCT(--(TRIM(field) = "")); (visibleGaps - strictGaps) / n )
3	I-25-2202	Yilmaz Metaalwaren
4	I-25-2203	Peeters Verhuur
5	I-25-2204	Smits Verpakking	KP200
6	I-25-2205	Janssen Laswerk
7	I-25-2206	Visser Kantoorservice	KP100
8	I-25-2207	Bosman Schoonmaak	KP300
9	I-25-2208	Dekker Transport
10

Formula cell (E2)InputMarks: extra spaceNote: C3 is empty. C4 and C9 each hold a single space. C6 holds empty text, as left behind by a formula that returned "" and was pasted as values.

Formula in E2

=LET(
  field, C2:C9,
  n, ROWS(field),
  strictGaps, COUNTBLANK(field),
  visibleGaps, SUMPRODUCT(--(TRIM(field) = "")),
  (visibleGaps - strictGaps) / n
)

=LET(
  field; C2:C9;
  n; ROWS(field);
  strictGaps; COUNTBLANK(field);
  visibleGaps; SUMPRODUCT(--(TRIM(field) = ""));
  (visibleGaps - strictGaps) / n
)

Result: 25%25%

Step by step

LET reads its arguments in pairs: a name, then a value. field stands for the range C2:C9 and n for ROWS(field), which is 8.
strictGaps is COUNTBLANK(field). It counts the truly empty C3 and the empty text in C6, but not the spaces in C4 and C9. The value is 2.
visibleGaps is SUMPRODUCT(--(TRIM(field) = "")). TRIM turns the spaces into empty text, and the comparison is TRUE for C3, C4, C6 and C9. The double minus makes them 1 and SUMPRODUCT adds them: 4. COUNTBLANK cannot be used here, because it needs a reference and TRIM builds an array.
The last argument of LET is the calculation: (4 - 2) / 8 = 0.25.
Read against the completeness figures: 1 - 2/8 = 75% by COUNTBLANK, 1 - 4/8 = 50% when spaces count as gaps. The plain count overstates completeness by 25 percentage points.

Watch out: The mirror-image mistake: ROWS(C2:C9) - COUNTA(C2:C9) looks like a gap count but returns 1, not 2. COUNTA counts the empty text in C6 as content, while COUNTBLANK counts it as a gap, so the two functions disagree on that cell.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The two counts measure different things in the same range: COUNTBLANK follows what the cell contains, the TRIM test follows what the reader sees. LET names both, so the last line reads like the sentence of the finding.

Do not use this construction when the column is already clean or you only need the strict count: a bare COUNTBLANK is clearer there. To avoid the second count altogether, clean the column first (for example in Power Query), at the price of changing the data you are testing.

More to watch out for

TRIM leaves a non-breaking space (CHAR(160)) in place. A cell with only that character counts as filled in both counts, and the overstatement is silently too low.

Variations

=1 - SUMPRODUCT(--(TRIM(C2:C9) = "")) / ROWS(C2:C9)

=1 - SUMPRODUCT(--(TRIM(C2:C9) = "")) / ROWS(C2:C9)

The completeness itself with spaces counted as gaps: 50%. Use it when you only need that figure and not the comparison.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Fewer gaps counted than you can see	The count is lower than the number of gaps you see. Some cells hold a single space, which looks empty but is not counted.	Count the cells that look empty with SUMPRODUCT(--(TRIM(A2:A9)="")). The Master example compares the two counts.
COUNTBLANK and ISBLANK disagree	COUNTBLANK and ISBLANK disagree: a cell with a formula that returns "" is counted by COUNTBLANK, while ISBLANK says FALSE for it.	Know which question you are asking. COUNTBLANK asks 'how many cells show nothing as a result', ISBLANK asks 'is this cell truly empty'. State which one your finding rests on.
Formula rejected with two ranges	COUNTBLANK(B2:B9, D2:D9) is rejected, because the function takes exactly one range.	Add two COUNTBLANK calls, COUNTBLANK(B2:B9) + COUNTBLANK(D2:D9), or use one range that covers both columns when the column in between should be counted as well.