Excel Challenge 15 : Count Blank Records

What You Will Learn

Your team contact list has five members in A2:A6. Each person has a phone number in column B and an email address in column C. Some cells are empty.

You need two things: summary counts to measure how big the data gap is, and row-level flags to know which specific records need follow-up.

B2:B6 — phone numbers (two are missing)
C2:C6 — email addresses (two are missing)
D2:D6 — your row flag formula goes here
B9 — total missing phones
B10 — total missing emails

This challenge uses COUNTBLANK for the summary counts and ISBLANK with IF for the row flags.

The image below shows the full sheet: team members in column A, phone numbers in column B, email addresses in column C, status flags in column D, and the audit section at the bottom.

Team roster with phone and email columns, status flags, and audit summary at the bottom.

The Summary Counts

COUNTBLANK scans a range and returns the number of empty cells. It is the quickest way to answer "how many are missing?":

=COUNTBLANK(B2:B6)

This tells you that two phone numbers are missing. The same formula pointed at column C tells you two emails are missing too.

The counterpart of COUNTBLANK is COUNTA, which counts non-empty cells instead of empty ones. Together they give a complete picture: COUNTBLANK shows what is missing, COUNTA shows what is present. If one column has 5 people but only 3 email addresses, COUNTBLANK returns 2 (missing) and COUNTA returns 3 (filled).

The Row Flags

The summary numbers tell you how big the problem is. The D column tells you exactly where. Each row checks its own email cell and labels itself:

=IF(ISBLANK(C2),"MISSING","OK")

ISBLANK returns TRUE when the cell is empty. IF turns that into a readable label. Fill this down from D2 to D6, then filter column D for "MISSING" — you get a clean list of exactly which records need action, no manual scanning required.

Alternative: Using COUNTA Instead

If you prefer to think in terms of what is filled rather than what is missing, subtract the filled count from the total roster:

=COUNTA(A2:A6) - COUNTA(C2:C6)

This returns the same number as COUNTBLANK — it just approaches the question from the opposite direction.

COUNTA comparison gives the same gap count from the opposite direction.

COUNTBLANK and ISBLANK treat a cell with a space as filled, not blank. If imported data looks empty but has invisible characters, the checks will miss those cells. When that happens, clean the column with TRIM first, then run the blank checks.