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

C1=COUNTA(A2:A6)
ABC
1Units4
24
3n/a
47
5
62
C1=COUNTA(A1:A6)
ABC
1Units5
24
3n/a
47
5
62

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.

Related functions