The Problem
A contact list is only useful when the key fields are filled in. Missing phone numbers and email addresses turn a normal sheet into a follow-up project because the team cannot rely on it when they need to call or message someone quickly.
The hard part is not spotting one empty cell. The hard part is measuring the gaps across the whole list and seeing exactly which rows still need work. This challenge covers both.
How We Solve It
We handle the task from two angles.
- First, we count how many blanks exist in the phone and email columns.
- Second, we flag each row based on whether the email field is empty.
That gives us a clean summary at the top and an action list inside the table.
Method 1: COUNTBLANK for the totals
Method 1: Count empty cells in one column with COUNTBLANK.
COUNTBLANK is the fastest way to answer a simple question like, "How many phone numbers are missing?" You point it at a range and it returns the number of empty cells. That is exactly what we need for the summary section in this challenge.
This solves the summary part of the problem because it turns a messy visual scan into one clear count. Instead of checking each row by eye, the worksheet can return the total number of missing phone or email entries right away.
=COUNTBLANK(B2:B6)
Method 2: Compare counts with COUNTA
Method 2: Compare a full roster to a partly filled column to estimate missing entries.
If one column should have a value for every person, you can compare the filled roster to the filled data column. That pattern is useful when your sheet structure makes a direct blank count less convenient or when you want to explain the gap in terms of missing records.
This solves the same counting problem from another angle. By comparing how many people exist in the roster to how many actual entries are present in the contact column, the difference shows how many records are still incomplete.
=COUNTA(A2:A6) - COUNTA(C2:C6)
Method 3: Flag each row with ISBLANK
Method 3: Mark each row so missing data is easy to filter later.
The summary tells us how much is missing, but the status column tells us where to look. By combining ISBLANK with IF, we can label each row as MISSING or OK. Once that column is filled, filtering the problem rows becomes easy.
This solves the row-level part of the problem. Instead of only knowing that some records are missing data, we can see exactly which rows need follow-up, which makes the cleanup list much more useful in practice.
=IF(ISBLANK(C2),"MISSING","OK")
Function Explanation
1. COUNTBLANK
COUNTBLANK counts empty cells inside a range. It is direct and readable, which makes it a great fit for the summary formulas in this challenge.
2. COUNTA
COUNTA counts non-empty cells. When you subtract the filled email count from the total roster count, the difference shows how many records are incomplete.
Learn more this functionCOUNTA
3. ISBLANK
ISBLANK checks whether a cell is empty. In this challenge, it powers the row-level flag so the sheet does more than just count the gaps.
One thing to watch for is fake blanks. A cell that contains a space is not truly blank, so a quick cleanup step may be needed if imported data looks empty but does not count as empty.
Count the missing phone numbers and email addresses first, then fill the status column so each row clearly shows whether the email field is complete or still missing.