The COUNTA function
Counts the cells that are not empty: numbers, text, logical values and errors.
How to use COUNTA
COUNTA counts the cells that have something in them. A cell can hold a number, a name, TRUE or an error, and COUNTA counts them all. Only truly empty cells are skipped. Think of counting the taken seats in a cinema, no matter who is sitting there.
Use it to find out how many names are on a staff list, how many orders have a customer filled in, or how many rows of a form have been answered. =COUNTA(A2:A6) counts the filled cells in A2:A6. If you include the heading row, as in =COUNTA(A1:A6), the heading counts as one too.
COUNTA checks whether a cell is filled, not what it holds. A formula that returns empty text, like =IF(B2="","",B2), counts as filled even though the cell looks blank. A single space counts too.
To count only numbers, use COUNT. To count the empty cells, use COUNTBLANK. To count the filled cells that meet a condition, use COUNTIF or COUNTIFS.
Syntax
COUNTA(value1, [value2], ...)- value1
- The first value, cell or range.
- value2 (optional)
- More values or ranges.
Examples
| A | B | C | |
|---|---|---|---|
| 1 | Units | 4 | |
| 2 | 4 | ||
| 3 | n/a | ||
| 4 | 7 | ||
| 5 | |||
| 6 | 2 |
| A | B | C | |
|---|---|---|---|
| 1 | Units | 5 | |
| 2 | 4 | ||
| 3 | n/a | ||
| 4 | 7 | ||
| 5 | |||
| 6 | 2 |
Common COUNTA mistakes
COUNTA counts cells that look empty
Those cells hold a formula that returns empty text, or a space you typed by accident. COUNTA sees them as filled. Count the filled cells another way with
=ROWS(A2:A6)-COUNTBLANK(A2:A6), because COUNTBLANK treats empty text as blank. Delete stray spaces with the Delete key.My count is one too high
The range probably includes the heading row. Start the range at the first row of data, like
=COUNTA(A2:A6).COUNTA counts numbers and errors, but I only want text
COUNTA counts everything that is filled. To count only the cells with text, use
=COUNTIF(A2:A6,"*").
Practice COUNTA
Questions
How do I count non-empty cells in Excel?
Use COUNTA on the range: =COUNTA(A2:A100). It counts every cell with a number, text, TRUE or FALSE, or an error, and skips the truly empty ones.
What is the difference between COUNT and COUNTA?
COUNT counts only numbers and dates. COUNTA counts every cell that is not empty, so a list of names gives the right count with COUNTA and 0 with COUNT.
Does COUNTA count hidden rows?
Yes, COUNTA counts hidden and filtered-out rows too. To count only the visible ones, use =SUBTOTAL(103,A2:A100).
Does COUNTA count zeros?
Yes. A zero is a value, so the cell counts as filled. To leave zeros out, use =COUNTIF(A2:A100,"<>0") on a column of numbers.