Statistical
COUNTA in Excel:syntax, examples and errors
COUNTA counts the cells that are not empty, whatever they hold. In Spanish Excel it is called CONTARA. Syntax: =COUNTA(value1, [value2], ...).
Syntax
=COUNTA(value1, [value2], ...)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
value1 | Required. The range you want to count. Example: A2:A8. |
value2, ... | Optional. More ranges or values, up to 255. Example: B2:B8. |
How do you use COUNTA? A step-by-step example
COUNTA counts the cells that hold something. In this example you have the signup list for a course, with some gaps, and you want to know how many rows are filled in.
| A | B | |
|---|---|---|
| 1 | Name | Paid |
| 2 | Lucy | 25 |
| 3 | Mark | "25" (stored as text) |
| 4 | ||
| 5 | ␣ (a space) | pending |
| 6 | Daniel | 30 |
| 7 | ="" (formula that returns empty text) | |
| 8 | 0 | 0 |
=COUNTA(A2:A8) returns 6.
A2:A8is the range.- COUNTA counts every cell that is not empty: Lucy, Mark, the space, Daniel, the formula and the 0.
- Only A4 is truly empty.
The result is surprising because there are only 3 names. The space in A5 and the formula in A7 do not show, but they count. That is why, before counting, it is worth cleaning the column.
What does COUNTA count and what does it skip?
| The cell holds | COUNTA | COUNT | COUNTBLANK |
|---|---|---|---|
| Text (Lucy) | Counts | No | No |
| A number (25) | Counts | Counts | No |
| A number stored as text ("25") | Counts | No | No |
| 0 | Counts | Counts | No |
| A space | Counts | No | No |
| A formula that returns "" | Counts | No | Counts |
| Nothing | No | No | Counts |
With the data in the example:
| Formula | Result |
|---|---|
=COUNTA(A2:A8) | 6 |
=COUNT(B2:B8) | 3 |
=COUNTA(B2:B8) | 5 |
=COUNTBLANK(A2:A8) | 2 |
=COUNTIF(A2:A8,"<>") | 6 |
Look at A7: COUNTA counts it as filled and COUNTBLANK as empty. If you add them up, one cell is counted twice.
COUNTA, COUNT or COUNTIF?
- COUNTA, to know how many rows have data: signups, answers, orders.
- COUNT, to know how many numbers there are: scores entered, amounts recorded.
- COUNTIF, to count with a condition: the ones that say "pending", the ones over 100.
A common use of COUNTA is to hand the number of rows to another function. For example, =INDEX(D2:D6,COUNTA(D2:D6)) brings back the last value of a column with no gaps (see INDEX).
How does COUNTA show up in an Excel test?
In a job interview it is often in the denominator of a percentage: "What percentage of the signups have paid?" The answer combines two counts, such as =COUNT(B2:B50)/COUNTA(A2:A50). What they look for: that you tell COUNT and COUNTA apart.
In a skills test:
A1:A4 holds "Ana", 5, an empty cell and 0. What does
=COUNTA(A1:A4)return? a) 1 · b) 2 · c) 3 · d) 4
Answer: c. It counts "Ana", the 5 and the 0. The empty cell does not count. To add instead of count, see SUM.
Practice COUNTA
The English exercises are on the way. Until then, copy the signup list into a blank sheet, type a space in one empty-looking cell and watch the count move by one. Then use AVERAGE on the same column to see how blanks behave there.
Available in: all versions of Excel. Source: Microsoft: COUNTA function. Results calculated with Formulo's engine.
Common mistakes
- It counts a cell that looks empty
- The cell has a space or a formula that returns "" (empty text). To Excel it is not empty. In the example, A5 has a space and A7 a formula ="": COUNTA counts both. Clear the cell with Delete or check the formula.
- It counts the zeros
- A 0 is not an empty cell. If the 0 means "no data", leave the cell empty or count with COUNTIF(range,"<>0").
- You wanted to count only numbers
- That is COUNT. COUNTA also counts text: in the payments column, =COUNTA(B2:B8) returns 5 and =COUNT(B2:B8) returns 3, because "25" stored as text and "pending" are not numbers.
Frequently asked questions
What is COUNTA called in Spanish?
CONTARA. In a Spanish Excel: =CONTARA(A2:A8).
What is the difference between COUNT and COUNTA?
COUNT only counts numbers (and dates, which are numbers). COUNTA counts any cell that is not empty: text, numbers, zeros, errors and the empty text of a formula.
How do I count the empty cells?
With COUNTBLANK: =COUNTBLANK(A2:A8) returns 2 in the example. It counts as empty the cells with nothing and the ones with a formula that returns "".
How do I count the cells with text, not the numbers?
With COUNTIF and an asterisk: =COUNTIF(A2:A8,"*") counts the cells with text. In the example it returns 5: it also counts the space and the "".
Can I count several columns at once?
Yes, with a wider range or several ranges: it counts the non-empty cells of both columns.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.