How to Use COUNTA and COUNTBLANK in Excel
Introduction
You need to count how many training courses each employee has completed. COUNT returns zero because every entry says "Complete," not a number. COUNTA and COUNTBLANK count what COUNT skips.
The Dataset
Here's a compliance tracker for a small team's required training:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 3 | Employee | Safety | Ethics | Software | Leadership |
| 4 | Nguyen | Complete | Complete | Complete | |
| 5 | Patel | Complete | Complete | ||
| 6 | Brooks | Complete | Complete | Complete | Complete |
| 7 | Tanaka | Complete | |||
| 8 | Rivera | Complete | Complete |
The HR manager needs to know who's fully compliant and which courses have the lowest completion rate before the quarterly audit.
Why COUNT Returns Zero
COUNT counts cells that contain numbers. The syntax is:
=COUNT(value1, [value2, ...])
=COUNT(B4:E4)
The result is 0. Every entry in this tracker is the text "Complete" or a blank cell. COUNT sees neither.
COUNT is built for numeric data like sales figures and test scores. For text, dates, or any mix of types, you need COUNTA.
COUNTA
COUNTA counts cells that aren't empty. Text, numbers, dates, whatever isn't blank. The syntax is:
=COUNTA(value1, [value2, ...])
- value1: A range of cells to count.
To count how many courses Nguyen has completed:
=COUNTA(B4:E4)
The result is 3. Nguyen has completed Safety, Ethics, and Leadership. Software is the gap.
| Employee | Completed |
|---|---|
| Nguyen | 3 |
| Patel | 2 |
| Brooks | 4 |
| Tanaka | 1 |
| Rivera | 2 |
Brooks is the only fully compliant employee. Tanaka has completed one course out of four.
To check completion rates across courses:
=COUNTA(B4:B8)
| Course | Completed | Out of 5 |
|---|---|---|
| Safety | 4 | 5 |
| Ethics | 3 | 5 |
| Software | 2 | 5 |
| Leadership | 3 | 5 |
Safety is nearly done. Software is falling behind.
Key takeaway: COUNTA counts every non-empty cell. If COUNT gives you zero because your data is text, COUNTA is the fix.
COUNTBLANK
COUNTBLANK counts empty cells:
=COUNTBLANK(range)
- range: A range of cells to check.
=COUNTBLANK(B4:E4)
The result is 1. Nguyen has one course remaining.
| Employee | Completed | Remaining |
|---|---|---|
| Nguyen | 3 | 1 |
| Patel | 2 | 2 |
| Brooks | 4 | 0 |
| Tanaka | 1 | 3 |
| Rivera | 2 | 2 |
Tanaka has the most to catch up on. If the audit deadline is close, that's where to focus.
To turn those counts into a completion percentage:
=COUNTA(B4:E4) / 4
Nguyen is at 75%. Tanaka is at 25%. Across the whole team, 12 out of 20 cells are filled, which puts overall compliance at 60%.
Key takeaway: COUNTBLANK counts empty cells. Pair it with COUNTA to find gaps and measure how complete your data is.
What COUNTA Counts
COUNTA counts any cell that isn't completely empty. That includes:
- Text ("Complete," "Yes," even a single space)
- Numbers (100, 0, -5)
- Dates and times
- Formulas that return "" (an empty string)
That last one catches people. A formula like =IF(A1>5, "Yes", "") returns an empty string when A1 is small, but COUNTA still counts the cell because it contains a formula. COUNTBLANK also counts that cell because it appears blank. This training tracker uses plain text, so you won't hit this. But if your range pulls from formulas, a cell can look empty and still get counted by COUNTA.
Conclusion
COUNT counts numbers. COUNTA counts anything that isn't empty. COUNTBLANK counts what is. Between the three of them, you can measure completion, find gaps, and audit any dataset where blanks carry meaning. For counting based on a condition like "count all entries from Marketing," see our guide on COUNTIF and SUMIF. For basic aggregation with numbers, check out our guide on SUM, AVERAGE, and COUNT.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.