COUNTA and COUNTBLANK Functions in Excel: Complete Tutorial with Examples

Need to count anything that’s filled in, or find empty cells? COUNTA counts all non-empty cells regardless of content type. COUNTBLANK does the opposite—it counts only empty cells. Together, they’re perfect for tracking data completion.

What Makes COUNTA and COUNTBLANK Useful

These functions handle different counting needs:

• COUNTA counts everything – Numbers, text, dates, errors, anything non-blank
• COUNTBLANK finds gaps – Identifies missing data or incomplete entries
• Data completion tracking – See what percentage of fields are filled
• Quality control – Find records with missing information
• Automatic validation – Flag incomplete forms or surveys

The Syntax

COUNTA:

=COUNTA(value1, [value2], ...)

Counts all non-empty cells.

COUNTBLANK:

=COUNTBLANK(range)

Counts empty cells in a range.

Example 1: Survey Response Tracking

Count how many people responded to each question:

Formula for Q1: =COUNTA(B2:B5)

Result: 3 (Person 4 skipped it)

Formula for blanks in Q1: =COUNTBLANK(B2:B5)

Result: 1 (one empty cell)

Example 2: Contact List Completion

Check which contact fields are filled:

Formula: =COUNTA(B2:E4)

Result: 7

Formula: =COUNTBLANK(B2:E4)

Result: 5

Total cells: 12. Filled: 7. Empty: 5.

Example 3: Different Data Types

See what COUNTA counts:

Formula: =COUNTA(B2:B8)

Result: 5

COUNTA counts the number, text, date, error, and zero. It ignores truly empty cells (even cells with just a space formula like =””).

Example 4: Form Completion Percentage

Calculate what percentage of a form is complete:

Formula: =COUNTA(B2:B7)/ROWS(B2:B7)*100

Result: 66.67%

Four out of six fields are filled. COUNTA counts filled cells, ROWS counts total.

Example 5: Finding Incomplete Records

Flag records missing required information:

Formula in E2: =IF(COUNTBLANK(B2:D2)>0, "Incomplete", "Complete")

Result: Complete (Alice has all fields)

Result in E3: Incomplete (Bob missing dept)

Check each row for any blanks.

Example 6: Attendance Tracking

Count attendance and absences:

Formula for Alex attendance: =COUNTA(B2:F2)

Result: 5 (all days marked)

Formula for Maya blanks: =COUNTBLANK(B3:F3)

Result: 1 (Thursday unmarked)

Key Differences

What COUNTA Counts

Included:

• Numbers: 1, 42, 3.14
• Text: “Hello”, “N/A”
• Dates: 1/1/2025
• Errors: #N/A, #VALUE!
• Logical values: TRUE, FALSE
• Zero: 0
• Formulas returning any value

Not Included:

• Empty cells
• Cells with formulas returning “” (empty text)

What COUNTBLANK Counts

Included:

• Empty cells with no content
• Cells with formulas returning “”

Not Included:

• Cells with spaces (unless from formula)
• Cells with any visible content
• Cells formatted but empty

Common Use Cases

Data Entry Progress Track form completion rates across multiple records.

Quality Assurance Find records with missing required fields.

Survey Analysis See response rates for each question.

Database Validation Identify incomplete customer or employee records.

Common Mistakes to Avoid

Confusing COUNT with COUNTA

• COUNT: Only numbers
• COUNTA: Everything non-blank
• Use COUNTA when text matters

Expecting COUNTBLANK to Count Zeros

• Zero (0) is a value, not blank
• COUNTBLANK ignores it
• Truly empty cells only

Range Requirements for COUNTBLANK

• COUNTBLANK needs a single range
• Can’t use multiple ranges like COUNTA
• =COUNTBLANK(A1:A10) works
• =COUNTBLANK(A1:A5, B1:B5) doesn’t work

Practical Formula Combinations

Total cells in a range:

=COUNTA(A1:A10) + COUNTBLANK(A1:A10)

Completion percentage:

=COUNTA(A1:A10)/(COUNTA(A1:A10)+COUNTBLANK(A1:A10))*100

Flag if any blanks exist:

=IF(COUNTBLANK(A1:A10)>0, "Incomplete", "Complete")

Start Using It

Open a spreadsheet with mixed data—some filled, some blank. Use COUNTA to count filled cells. Use COUNTBLANK to find gaps. Compare the results to understand your data completion.

These functions reveal what’s there and what’s missing.

Questions about COUNTA or COUNTBLANK? Want to explore conditional counting with COUNTIF? Let’s connect.