Excel COUNTBLANK Function

COUNTBLANK counts the number of empty cells in a range you specify. It returns a single number. The gotcha: a cell that looks empty because a formula returns "" still gets counted as blank, even though it technically contains a formula. A cell with a single space does not get counted. Same visual result, opposite answer.

Syntax

=COUNTBLANK(range)

Parameter	Required	Description
range	Yes	The cells you want checked for blanks. COUNTBLANK accepts only one contiguous range, unlike COUNTA or SUM, which accept multiple.

Basic Example

You're managing an order tracker and want to know how many rows are missing a ship date before the weekly report goes out.

=COUNTBLANK(D2:D50)

// D2:D50 = the Ship Date column for all current orders

D2:D50 is the range holding ship dates. COUNTBLANK scans every cell in that range and returns the count of cells that are empty. If 6 orders haven't shipped yet, the formula returns 6. No wildcards, no criteria to set up. Just a range.

How COUNTBLANK Works

What counts as truly blank

A cell counts as blank only when it has nothing in it at all: no text, no number, no space, no formula. An untouched cell, or one you've explicitly cleared with Delete, qualifies. Nothing else does.

Formulas that return an empty string still count

This is the part most people miss. If a cell contains =IF(B2="","",B2) and B2 is empty, the cell displays nothing, but it's not actually empty. It holds a formula. COUNTBLANK counts it anyway, because it evaluates the result of the cell, not whether the cell is occupied. Ablebits calls this behavior a little illogical, and it is, but it's consistent and predictable once you know it.

Spaces and zeros are not blank

Type a single space into a cell and COUNTBLANK will not count it. Spaces are text, even invisible text. Same goes for zeros: a cell containing 0 is a value, not a blank, so COUNTBLANK skips it. This matters most when data comes from a form or an import where blank fields get filled with a stray space instead of staying empty.

It only takes one range

COUNTBLANK doesn't accept a list of ranges the way COUNTA or SUM does. Try =COUNTBLANK(A2:A10, C2:C10) and Excel throws an error, because the function signature only has room for one argument. If you need blanks counted across two separate ranges, add two COUNTBLANK calls together instead.

Common Use Cases

Flag incomplete records before a report runs

You're pulling a weekly status report and want a quick check that no required fields are missing before you send it.

=COUNTBLANK(orders[ShipDate])   // returns count of orders with no ship date yet

Build a data-completeness dashboard

Combine COUNTBLANK with COUNTA to show what percentage of a form's responses are complete.

=COUNTA(responses) / (COUNTA(responses) + COUNTBLANK(responses))

// COUNTA(responses)      = cells that have anything in them
// COUNTBLANK(responses)  = cells with nothing in them
// result                 = percentage of fields filled in

Sum blanks across two sheets

Since COUNTBLANK won't take a 3D reference or multiple ranges in one call, add separate calls together to check blanks across two monthly sheets.

=COUNTBLANK(Jan!C2:C200) + COUNTBLANK(Feb!C2:C200)

// counts empty cells in column C on both the Jan and Feb sheets, combined

Conditional formatting trigger

Use COUNTBLANK inside an IF to warn when a batch of records isn't ready for review.

=IF(COUNTBLANK(D2:D50)>0, "Missing data", "Complete")

Handling Errors

COUNTBLANK doesn't throw errors on the kind of messy data COUNT or SUM might choke on. It's a forgiving function. The one place it does throw an error is when the range argument itself is malformed.

Common causes of #VALUE!:

• Passing two ranges separated by a comma, since COUNTBLANK only accepts one
• Referencing a closed external workbook incorrectly
• Passing a 3D reference across multiple sheets, like COUNTBLANK(Jan:Dec!A2:A10)

=COUNTBLANK(A2:A10) + COUNTBLANK(C2:C10)   // instead of COUNTBLANK(A2:A10, C2:C10)

There's nothing to wrap in IFERROR here. The fix is structural: split the ranges into separate COUNTBLANK calls and add the results, rather than trying to pass multiple ranges into one call.

Notes & Gotchas

What is the difference between COUNTBLANK, COUNT, and COUNTA in Excel?

COUNTBLANK counts empty cells. COUNT counts only cells containing numbers. COUNTA counts any cell that isn't empty, whether it holds text, numbers, dates, or errors. Together, COUNT and COUNTA cover the "has something" side of a range, while COUNTBLANK covers the "has nothing" side.

Does COUNTBLANK count cells containing a formula that returns an empty string ("")?

Yes. A cell with =IF(A2="","",A2) that displays nothing still gets counted as blank by COUNTBLANK, because the function evaluates what the cell shows, not whether a formula is present. This means a "blank-looking" cell built from a formula and a genuinely empty cell are indistinguishable to COUNTBLANK.

How do you count blank cells in Excel using COUNTIF instead of COUNTBLANK?

Use =COUNTIF(range, ""). This behaves almost identically to COUNTBLANK: it also counts cells with formulas returning "". If you need to exclude those pseudo-blank cells and count only cells that are structurally empty, use =COUNTIF(range, "=") instead. The extra = sign forces an exact match on a truly empty cell.

Can COUNTBLANK be used across multiple, non-contiguous ranges?

No. COUNTBLANK's syntax only accepts one range argument, unlike COUNTA or SUM, which accept several. To count blanks across two disconnected ranges or two different sheets, run COUNTBLANK on each range separately and add the results with a plus sign.

How do you count non-blank (filled) cells in Excel?

Use COUNTA. =COUNTA(range) counts every cell that contains anything at all: text, numbers, dates, logical values, or error values. If you only want to count cells with numbers, use COUNT instead, since COUNTA will also count text entries that COUNT ignores.

Does COUNTBLANK slow down large worksheets when applied to entire columns?

It can. A formula like =COUNTBLANK(A:A) forces Excel to scan all 1,048,576 rows in that column every time the sheet recalculates. On a small workbook this is instant. On a large model with thousands of formulas doing the same thing across full columns, it adds up. Scope the range to your actual data, like A2:A5000, instead of the whole column.

Do merged cells count as one blank cell or several?

A merged cell counts as a single cell, and only the top-left cell of the merge holds any value. If that top-left cell is empty, COUNTBLANK counts the whole merged block as one blank cell, not as however many rows or columns it visually spans. This can undercount blanks if you're expecting COUNTBLANK to reflect the merged cell's visual size.

Related Functions

Function	Use this when...
COUNTA	You want to count cells that have anything in them, not the ones that are empty.
COUNT	You only care about cells containing numbers, ignoring text and blanks entirely.
COUNTIF	You need to count blanks with a condition, or need the "=" criteria trick to exclude empty-string formulas.
COUNTIFS	You need to count blanks in one column only when another column meets a separate condition.