COUNTBLANK counts empty cells and cells whose formulas return empty text. Use a bounded range to count missing entries without including every unused row in the sheet.
COUNTBLANK function syntax
=COUNTBLANK(value1, [value2, ...])- value1: the first cell or range to inspect.
- value2 and later arguments: optional additional values or ranges.
- Empty strings count as blank; spaces, zero and FALSE do not.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Task | Owner |
|---|---|
| Plan | Maya |
| Draft | |
| Review | =”” |
| Publish | Leo |
| Archive |
Count missing owners
Enter this formula in A9. B3 and B6 are genuinely empty; B4 contains a formula returning empty text. All three count as blank.
=COUNTBLANK(B2:B6)Result: 3.
Count missing marks across a grid
Enter the attendance table at A20. The twelve attendance cells contain four gaps. COUNTBLANK counts cells across the whole rectangle, not the number of incomplete students.
| Student | Mon | Tue | Wed |
|---|---|---|---|
| Maya | P | P | |
| Leo | P | P | |
| Ana | P | ||
| Omar | P | P | P |
=COUNTBLANK(B21:D24)Result: 4.
Count blanks in separate ranges
You can supply separate ranges directly. This checks Monday and Wednesday, where one and one cells are blank, giving 2. The ranges should not overlap unless double counting is intended.
=COUNTBLANK(B21:B24,D21:D24)Result: 2.
Compare formula blanks with COUNTA
COUNTA counts the two owner names and the formula in B4, giving 3. COUNTBLANK also counts B4 because its result is empty text. The two counts therefore overlap.
=COUNTA(B2:B6)Result: 3.
Distinguish empty text from an empty cell
ISBLANK returns FALSE for B4 because the cell contains a formula, even though COUNTBLANK counts it as blank. Choose the function that matches your definition of missing data.
=ISBLANK(B4)Result: FALSE.
Count missing marks for each student
BYROW applies COUNTBLANK separately to each attendance row. The four results are 1, 1, 2 and 0, making it easy to identify students whose records need completion.
=BYROW(B21:D24,LAMBDA(r,COUNTBLANK(r)))Result: 1; 1; 2; 0.
Other Google Sheets articles you may also like