COUNTBLANK

Returns the number of empty cells.

Syntax:

COUNTBLANK(range)

Returns the number of empty cells in the cell range range. A cell that contains blank text such as spaces, or even text with zero length such as returned by ="", is not considered empty, even though it may appear empty.

Example:

COUNTBLANK(A1:B2)

returns 4 if cells A1, A2, B1 and B2 are all empty.

Advanced topic:

SUMPRODUCT(TRIM(CLEAN(A1:B2))="")

returns the count of cells that appear blank - that is, that are really empty or that contain blank text or zero length text.

SUMPRODUCT(ISTEXT(A1:B2);TRIM(CLEAN(A1:B2))="")

returns the count of cells containing blank text and zero length text.

Application:

Imagine you are a teacher and you have a grade book for your class. You want to quickly see how many students have not yet submitted a particular assignment. The cells for those students would be left blank.

Table: Grade Book

	Student Name	Quiz 1	Quiz 2	Midterm Exam	Final Exam	Project
	A	B	C	D	E	F
1	Alex	92	88	95	98
2	Brenda	78	85		82	90
3	Chris	95		92	90	85
4	David	88	90	85	88	92
5	Emily		92	90	95	98

In this table, the cells under the "Project" and "Quiz 2" columns are not completely filled. You can use the COUNTBLANK function to find out how many students haven't submitted these assignments.

Example Usage:

To find out how many students have not submitted the "Project," you would use the following formula:

COUNTBLANK(F1:F5)

Result:

1

The function would count the empty cell in the range QUESTION2!F1:QUESTION2!F5 (the cells under the "Project" column), and the result would be 1. This tells you that one student (Alex) has not submitted the project.

You could also use it to check how many students haven't taken "Quiz 2":

COUNTBLANK(C1:C5)

Result:

1

The function would count the empty cell in the range QUESTION2!C1:QUESTION2!C5 (the cells under the "Quiz 2" column) and the result would be 1. This shows that one student (Chris) missed Quiz 2.