Excel guide · 4 min read

How to count blank cells in Excel

Q: How do I count non-blank cells in Excel?

Use =COUNTA(range). It counts every cell that is not empty, including text, numbers, errors and formulas that return "". To skip those empty strings, use =COUNTIF(range,"?*") for text or SUMPRODUCT(--(LEN(range)>0)).

Quick answerUse =COUNTBLANK(C2:C9) to count the empty cells in a range. To count blanks only in rows that meet a condition, use COUNTIFS with "" as the criteria, for example =COUNTIFS(A2:A9,"<>",C2:C9,""). COUNTBLANK also counts formulas that return "", but it does not count cells that hold a space.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D
1	Order	Customer	Shipped	Action
2	SO-4101	Harper & Co	Oct 12, 2026
3	SO-4102	Lumen Dental		Chase
4	SO-4103		Oct 14, 2026
5	SO-4104	Brightline Gym	Oct 15, 2026
6	SO-4105			Chase
7	SO-4106	Oakridge Cafe	Oct 19, 2026
8	SO-4107	Pinecrest Vets		Chase
9	SO-4108	Harper & Co	Oct 21, 2026

Download this sheet as an Excel practice file (.xlsx)

COUNTBLANK: the quick answer

This order list has a Shipped date for each order that has gone out. Three orders have no date yet. COUNTBLANK takes one range and returns how many of its cells are empty.

Point it at a column that should always be filled. Counting blanks in an optional column (like Notes) tells you little.

=COUNTBLANK(C2:C9)

Result: 3

3 orders (SO-4102, SO-4105 and SO-4107) have no ship date yet.

Blanks that are not really empty: formulas returning ""

Column D holds =IF(C2="","Chase",""). On shipped orders it returns an empty string, so the cell looks blank. COUNTBLANK counts those cells as blank, which is usually what you want: it counts what you see.

ISBLANK is stricter. It is TRUE only for a cell with nothing in it at all, so a formula cell is never blank to ISBLANK. Pick the one that matches your question: "how many look empty" is COUNTBLANK, "how many have never been filled in" is ISBLANK.

=COUNTBLANK(D2:D9)

Result: 5

5: the five shipped orders, where the formula returns "". SUMPRODUCT(--ISBLANK(D2:D9)) would return 0 here, because every cell in D has a formula.

Cells with a space look blank but are not

Customer for SO-4105 was pasted as a single space. It looks empty, but COUNTBLANK, COUNTIF and ISBLANK all see text. That is why a blank count can come out lower than what you see on screen.

To count cells that are empty or hold only spaces, trim each cell first and test for an empty result. SUMPRODUCT handles the array in any version of Excel.

=SUMPRODUCT(--(TRIM(B2:B9)=""))

Result: 2

2: SO-4103 has no customer at all, and SO-4105 holds only a space. COUNTBLANK(B2:B9) would say 1.

Count blanks with a condition (COUNTIFS)

COUNTIFS treats "" as "is empty" and "<>" as "is not empty". Combine them to count blanks only in rows that matter, such as orders that exist but have no ship date. This also stops empty rows at the bottom of a table from inflating the count.

Add more pairs for more conditions, for example a customer name in a third pair.

=COUNTIFS(A2:A9,"<>",C2:C9,"")

Result: 3

3 real orders are missing a ship date.

Count rows with a blank in any of several columns

COUNTIFS joins its conditions with AND, so it cannot answer "blank in B or blank in C". Add the two tests inside SUMPRODUCT instead. Adding gives 2 when both are blank, so test for >0 to count each row once.

=SUMPRODUCT(--((B2:B9="")+(C2:C9="")>0))

Result: 4

4 orders are incomplete: three with no ship date and SO-4103 with no customer. SO-4105 counts once because its customer cell holds a space, not nothing.

Find and select the blanks without a formula

To fill or highlight the empty cells rather than count them, let Excel select them for you. The status bar does not show a blank count, but once they are selected you can type a value and press Ctrl+Enter to fill them all at once.

Select the range, for example C2:C9.
Press F5 (or Ctrl+G) and click Special, or go to Home > Find & Select > Go To Special.
Choose Blanks and click OK.
Type a value such as Pending and press Ctrl+Enter to fill every selected cell.

Common mistakes

Giving COUNTBLANK several ranges

COUNTBLANK accepts only one range. =COUNTBLANK(B2:B9,C2:C9) is rejected. Add two counts together: =COUNTBLANK(B2:B9)+COUNTBLANK(C2:C9).

Counting a whole column

=COUNTBLANK(C:C) counts over a million empty rows below your data. Use the exact range, or turn the data into a table (Ctrl+T) and use the column name so the range grows with it.

Zeros treated as blanks

A cell showing 0, or hidden by a format like 0;-0;;@, is not blank and is not counted. If you need zeros counted as missing, add COUNTIF(C2:C9,0).

Using COUNTA minus COUNT to find blanks

That counts text cells, not blanks. For blanks, use COUNTBLANK, or the total number of cells minus COUNTA when no formulas return "".

FAQ

Does COUNTBLANK count cells with formulas that return ""?

Yes. A formula returning an empty string counts as blank for COUNTBLANK and for COUNTIF(range,""). ISBLANK says FALSE for the same cell because it contains a formula.

How do I count non-blank cells in Excel?

Use =COUNTA(range). It counts every cell that is not empty, including text, numbers, errors and formulas that return "". To skip those empty strings, use =COUNTIF(range,"?*") for text or SUMPRODUCT(--(LEN(range)>0)).

Why is COUNTBLANK returning 0 when I can see empty cells?

The cells probably hold a space, a non-breaking space from a web page, or an apostrophe. Click one and look at the formula bar. Use the TRIM version in this guide, or clear them with Find and Replace.

How do I count blank cells in a filtered list?

COUNTBLANK counts hidden rows too. Use a helper column with =IF(C2="",1,0) and then =SUBTOTAL(109,E2:E9), which only adds visible rows.

Functions used

Related guides

Updated November 2, 2026