Function guides · Statistical
COUNTA function
Counts the cells that are not empty.
Syntax
=COUNTA(value1, [value2], ...)
| Argument | What it is |
|---|---|
value1 | A cell reference or range. |
value2, ... | Optional. Up to 255 more. |
What COUNTA does
COUNTA counts every cell in a range that is not empty. The A is for “all”: text, numbers, dates, logical values and even errors all count.
Use it when the question is “how many entries are there” rather than “how many numbers”. A list of names, order IDs or email addresses is COUNTA territory, because COUNT would return 0 for all of them.
The one thing it cannot see is a cell that only looks empty. A space is content.
COUNTA examples
1. How many customers are listed
| A | |
|---|---|
| 1 | Customer |
| 2 | Hale Pools |
| 3 | Kona Print |
| 4 | |
| 5 | Maui Tile |
| 6 | Oahu Lumber |
| 7 | Pearl Cafe |
| 8 | 5 |
COUNTA counts cells that hold anything: names, numbers, dates, even an error. Five names, one blank, so the answer is 5.
The header cell is left out of the range. Start the range at the first data row, not at the label.
2. Number the next row
| A | |
|---|---|
| 1 | ID |
| 2 | E-101 |
| 3 | E-102 |
| 4 | E-103 |
| 5 | |
| 6 | E-104 |
A common trick is to use COUNTA to find the next ID. With three IDs in column A, the next one is the count plus one.
It only works while the list has no gaps, so test it on your data before trusting it.
3. COUNTA against COUNT on mixed data
| A | |
|---|---|
| 1 | Reading |
| 2 | 14.2 |
| 3 | n/a |
| 4 | 15.8 |
| 5 | |
| 6 | |
| 7 | 3 |
The same range gives different answers. COUNT sees the two numbers and ignores "n/a". COUNTA sees all three filled cells.
The gap between the two is the number of text entries, which is often what you are hunting for.
Common COUNTA mistakes
- Cells that look empty but are not. A cell with a single space, or a formula returning "", counts as filled. If COUNTA is one too high, click the blank-looking cells and check the formula bar.
- Starting the range at the header. The header is text and gets counted, so the answer is one too high. Count from the first data row.
- Expecting COUNTA to count unique values. It counts entries, so a name that appears three times counts three times. For distinct counts use ROWS(UNIQUE(range)) in current Excel.
- Using it on a range with a total row. A SUM at the bottom is a filled cell. Keep the total row out of the range.
COUNTA on Excel job tests
When a prompt asks "how many customers are listed" or "how many rows of data are there", reach for COUNTA. The usual trouble is stray cells: a note in the corner, a trailing space, a repeated header partway down. Any of them adds one to the count. Press Ctrl+Down on the first cell to see where the block really ends.
Wording matters. "How many people were hired in 2025" needs COUNTIF, not COUNTA, which would return everyone. And if the task asks for the number of blanks, use COUNTBLANK rather than subtracting COUNTA from the row count, because COUNTBLANK still works when the data changes.
Related functions
COUNTA questions
- Does COUNTA count blank cells?
- No. It counts every cell that has any content. A truly empty cell is skipped, though a cell with a space or an empty-string formula result is counted.
- Does COUNTA count zeros and errors?
- Yes to both. Only genuinely empty cells are left out.
- How do I count unique entries?
- In current versions of Excel,
=COUNTA(UNIQUE(A2:A100))counts distinct non-blank values. Older versions need a SUMPRODUCT with COUNTIF. - What is the shortcut for counting selected cells?
- Select the cells and look at the status bar. It shows Count, which is COUNTA, and Numerical Count, which is COUNT.
Practice COUNTA
Try it in a working grid: the free lesson COUNT and COUNTA (module 4 of Excel from Zero) has checked COUNTA exercises.
Practice this function