Excel Challenge 13: Count Blank Records

Challenge Scenario

In database management and user registration auditing, incomplete user profiles with missing information (such as missing email addresses or unconfirmed phone numbers) create communication gaps and lead quality issues.

Before exporting customer records to the CRM, data administrators audit registration tables to count empty cells and verify overall data completeness.

As a Database Quality Specialist, your tasks are:

Missing Fields per User (Column D): Count blank cells across Name, Email, and Phone (A2:C2) for each customer row using the COUNTBLANK function.
Total Blank Cells (Cell B8): Count all empty cells across the entire dataset table (A2:C5) using COUNTBLANK.
Incomplete Profiles (Cell C8): Count how many customer rows have 1 or more missing fields using COUNTIF(D2:D5, ">0").
Total Audited Users (Cell D8): Count total customer records using COUNTA(A2:A5).

Your Sheet Layout

Here is an overview of the registration data quality audit sheet:

Registration Records (Columns A to C): Contains user profiles showing Full Name, Email Address, and Phone Number with occasional blank entries.
Row Missing Counter (Column D): Counts how many fields are missing in that specific row.
Database Health Summary (Rows 7 & 8): Summary KPIs showing total empty cells, total incomplete customer accounts, and total registered users.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:C5`	Full Name, Email, Phone	User Records	Raw customer profile submissions	Incoming customer registration records.
`D2:D5`	Missing Fields	Quality Audit	`=COUNTBLANK(A2:C2)`	Counts blank cells across Columns A, B, and C for each row.
`B8`	Total Blank Cells	Summary KPI	`=COUNTBLANK(A2:C5)`	Counts all empty fields across the full table range (3).
`C8`	Incomplete Profiles	Summary KPI	`=COUNTIF(D2:D5, ">0")`	Counts user profiles with at least 1 missing field (2).
`D8`	Total Audited Users	Summary KPI	`=COUNTA(A2:A5)`	Counts total user rows in the audit list (4).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: D2-D5

In Column D (D2:D5), output "INCOMPLETE" if B2 or C2 is blank, otherwise "COMPLETE".

Objective #2

Cell: B8

In cell B8, count missing phone numbers using COUNTBLANK(B2:B5).

Objective #3

Cell: C8

In cell C8, count missing email addresses using COUNTBLANK(C2:C5).

Objective #4

Cell: D8

In cell D8, count fully complete leads using COUNTIF(D2:D5, "COMPLETE").

Solution & Formula Breakdown

Let's examine how Excel identifies and aggregates missing data cells:

1. Counting Missing Values with COUNTBLANK (D2:D5)

=COUNTBLANK(A2:C2)

The COUNTBLANK(range) function counts empty cells in the specified horizontal range:

Row 2 (John Miller): All 3 fields provided → COUNTBLANK(A2:C2) outputs 0.
Row 3 (Sarah Connor): Missing Email → COUNTBLANK(A3:C3) outputs 1.
Row 4 (Bruce Wayne): All 3 fields provided → COUNTBLANK(A4:C4) outputs 0.
Row 5 (Clark Kent): Missing Email and Phone → COUNTBLANK(A5:C5) outputs 2.

2. Summary Audit KPIs (B8, C8, D8)

Total Blank Cells (Cell B8): =COUNTBLANK(A2:C5) counts all empty cells in the entire 4x3 grid, returning 3.
Incomplete Profiles (Cell C8): =COUNTIF(D2:D5, ">0") checks Column D for values greater than 0, returning 2.
Total Audited Users (Cell D8): =COUNTA(A2:A5) counts non-empty user rows, returning 4.

In COUNTIF(D2:D5, ">0"), remember that comparison operators like ">0", ">=10", or "<>0" must be wrapped in double quotes inside the criteria parameter.