Excel COUNTA Function

COUNTA counts the number of cells in a range that contain any value. That includes numbers, text, dates, logical values like TRUE or FALSE, and even error values. It does not count cells that are genuinely empty. The gotcha worth knowing up front: a cell that looks blank but holds an empty string returned by a formula still gets counted, which can quietly inflate your totals.

Syntax

=COUNTA(value1, [value2], ...)

Parameter	Required	Description
`value1`	Yes	The first range or value to count. Usually a range of cells, but it can also be a single value typed directly into the formula.
`value2, ...`	No	Additional ranges or values to include in the same count. COUNTA accepts up to 255 arguments total.

Basic Example

You're tracking an order form where column C holds delivery notes for each order. Some rows have a note, some don't. To find out how many orders actually have a note attached:

=COUNTA(C2:C50)

// C2:C50 = the range of order notes, some filled, some blank

COUNTA scans C2 through C50 and counts every cell that isn't empty, whether that cell holds a one-word note or a full sentence. If 32 out of 49 rows have text in them, the formula returns 32. Blank rows are skipped entirely.

How COUNTA Works

What counts as "not empty"

COUNTA treats anything other than a truly empty cell as a value. Numbers, text, dates, TRUE/FALSE, and error values like #N/A all count.

ℹ

An empty string is text with zero visible characters, often produced when a formula like =IF(A1>0,A1,"") returns nothing to look at but still technically holds a value.

⚠

COUNTA counts cells that look empty but actually contain an empty string returned by a formula, or a stray space typed by accident. This can make a range look fully filled in when several rows are functionally blank. If your count seems too high, check for formulas returning "" with Find & Replace or Go To Special > Blanks.

COUNTA counts across multiple columns and rows in one pass

A single COUNTA formula can span an entire block of cells, not just one column. =COUNTA(A2:C50) counts every non-blank cell across all three columns at once, treating the whole rectangle as one pool.

COUNTA works across sheets with 3D references

COUNTA can reach across multiple worksheets in a single formula. If Sheet1, Sheet2, and Sheet3 each hold one month of survey responses in column A, this counts every response across all three months at once:

=COUNTA(Sheet1:Sheet3!A2:A100)

Add or rename a sheet inside that Sheet1:Sheet3 span and the reference updates automatically. Delete the first or last sheet in the span, though, and the reference breaks.

COUNTA works with tables and spilled array ranges

Point COUNTA at a structured reference like =COUNTA(Orders[Notes]) and it counts non-blank entries in that table column, adjusting automatically as rows are added or removed. It also works with a spilled dynamic array using the # operator: =COUNTA(D2#) counts every value in whatever range a formula in D2 has spilled into, without you needing to know the exact size in advance.

Common Use Cases

Count survey responses that were actually submitted

You have 214 rows in a feedback form, but only some respondents typed a comment.

=COUNTA(feedback_comments)   // returns the number of respondents who left any comment

Combine COUNTA with SUBTOTAL to respect filters

Straight COUNTA ignores whether rows are hidden or filtered. It counts everything in the range regardless. If you're working a filtered order list and only want a count of visible non-blank cells:

=SUBTOTAL(103, order_notes)

// 103 = the function code for COUNTA-style counting that ignores hidden rows

✓

SUBTOTAL with function code 103 behaves like COUNTA but skips manually hidden and filtered-out rows. Regular COUNTA never does this on its own.

Exclude error values before counting non-blank cells

A column of formulas sometimes throws #N/A or #VALUE! errors, and you don't want those inflating your "filled in" count.

=COUNTA(sales_data) - SUMPRODUCT(--ISERROR(sales_data))

// SUMPRODUCT(--ISERROR(sales_data)) = the number of error cells to subtract out

Handling Errors

COUNTA rarely throws an error on its own, since it's designed to count anything, including errors themselves. When it does fail, it's almost always a reference problem rather than a data problem.

Common causes of #REF!:

The range you referenced was deleted, along with the rows or columns inside it
A 3D reference lost its first or last sheet because that sheet was deleted

Common causes of #VALUE!:

You supplied more than 255 arguments directly in the formula (rare, but it happens with long manually typed lists)

A #REF! error here means the reference itself is broken. Wrapping the formula in IFERROR just hides that and replaces a clear error with a vague fallback message, so you lose the signal that something needs fixing. Instead, rebuild the reference directly: re-add the deleted sheet, restore the missing range, or update the 3D span to point at sheets that still exist.

Notes & Gotchas

What is the difference between COUNT and COUNTA in Excel?

COUNT only counts cells containing numbers, including dates and times stored as serial numbers. COUNTA counts cells containing any value at all: text, numbers, logicals, or errors. Use COUNT when you specifically need a numeric tally, like counting how many invoices have a dollar amount entered. Use COUNTA when you need to know how many cells simply aren't empty, which makes it the better choice for tracking form completion, survey responses, or any list where text matters as much as numbers.

What is the difference between COUNTA and COUNT in the status bar?

Select a range of cells and Excel's status bar shows a "Count" figure, which mirrors COUNTA: it reflects every non-blank cell in the selection. If the selection also contains numbers, a second figure labeled "Numerical Count" appears alongside it, matching what COUNT would return. Selecting a mix of text and numbers lets you compare the two values instantly without writing a formula.

What is the difference between COUNT, COUNTA, and COUNTBLANK?

COUNT counts numeric cells only. COUNTA counts every non-blank cell regardless of type. COUNTBLANK counts empty cells, plus any cell that looks blank but actually holds a formula returning an empty string. In a clean range with no formula-generated blanks, COUNTA plus COUNTBLANK should equal the total number of cells.

Is it possible to count cells across multiple columns with COUNT or COUNTA?

Yes. Both functions accept a range that spans several columns and rows at once, or multiple separate ranges as arguments. =COUNTA(A2:C50) counts non-blank cells across three columns in a single formula. You can also skip a column entirely by listing ranges separately, like =COUNTA(A2:A50, C2:C50).

Does COUNTA include hidden or filtered rows in the count?

Yes, and this trips people up constantly. COUNTA counts every cell in its reference range whether the row is visible, manually hidden, or hidden by a filter. If you need a count that respects filtering, use SUBTOTAL(103, range) or AGGREGATE(3, 5, range) instead.

Why does COUNTA(UNIQUE(range)) overcount when the range has blank cells?

UNIQUE treats blank cells as a single value, and that value typically evaluates to 0 in the returned array. COUNTA then counts that 0 as one more unique entry, adding one extra to your true count of distinct values. Filter out blanks before counting to fix it: =COUNTA(UNIQUE(FILTER(range, range<>""))).

Related Functions

Function	Use this when...
`COUNT`	You need to count numeric cells only, ignoring text and blanks entirely.
`COUNTIF`	You need to count non-blank cells that also match a specific condition.
`SUBTOTAL`	You need a COUNTA-style count that ignores hidden or filtered rows.