COUNTA Generator
Count every cell in a range that is not empty, including text, numbers, dates, and errors.
COUNTA
Count every cell in a range that is not empty, including text, numbers, dates, and errors.
The cells to check. COUNTA counts everything that is not truly empty.
=COUNTA(A2:A100)Worked Example
A survey response column A2:A21 holds 20 cells, and 3 respondents skipped the question, leaving those cells empty.
=COUNTA(A2:A21)Returns: 17 - the number of cells that contain an answer.
Checks Before You Paste
- •COUNT tallies numbers only, COUNTA tallies anything at all - text, dates, TRUE/FALSE, and even error values such as #N/A. On a column of names COUNT returns 0 while COUNTA returns the number of names.
- •A formula that returns "", such as =IF(A2="","",A2), leaves a cell that looks blank but still counts. To ignore those, use =SUMPRODUCT(--(A2:A100<>"")), which counts only cells with real content - though unlike COUNTA it returns an error if any cell in the range holds one.
- •COUNTA takes no criteria - use COUNTIF or COUNTIFS when the count needs a condition, as in =COUNTIF(A2:A100,">100"). COUNTA does accept several ranges at once, as in =COUNTA(A2:A100, C2:C100), and whole-column refs like =COUNTA(A:A) work but include the header row.
How COUNTA works
Availability: COUNTA is in every version of Excel, in Excel for Mac and for the web, and in Google Sheets with identical behaviour. So are its neighbours COUNT, COUNTBLANK and COUNTIF. Nothing here depends on Microsoft 365 or dynamic arrays.
COUNTA counts cells that are not empty. That includes text, numbers, dates, times, TRUE and FALSE, and - a detail people rarely expect - error values such as #N/A and #DIV/0!. The only thing it does not count is a cell with nothing in it at all. Its sibling COUNT is stricter: it counts numbers and dates only, so on a column of names COUNT returns 0 while COUNTA returns the number of names.
The word "empty" is doing a lot of work in that definition, and it means empty in Excel's technical sense: no value and no formula. A cell holding a formula that returns an empty string, such as =IF(B2="", "", B2), is not empty - it holds a formula whose result is a zero-length text string, and COUNTA counts it. So does a cell containing a single space typed by hand. Both look blank on screen, which is why a COUNTA total so often exceeds the number of entries anyone can see.
When that distinction matters, =SUMPRODUCT(--(A2:A100<>"")) is the usual replacement. It counts cells that hold something other than an empty string, so formula-blanks are excluded. It is not a drop-in equivalent: it returns an error if any cell in the range contains one, whereas COUNTA counts errors happily. Pick based on which failure you would rather have - a wrong number or a visible error.
COUNTA takes no criteria at all. The moment your count needs a condition, you want COUNTIF or COUNTIFS. What it does take is up to 255 separate arguments, so =COUNTA(B2:B50, E2:E50, H2:H50) counts three disjoint ranges in one call, and whole-column references like =COUNTA(A:A) work fine but include the header row in the total.
Syntax
=COUNTA(value1, [value2], ...)| Argument | Required | What it does |
|---|---|---|
value1 | Required | The first range, cell, array or value to count. A range is the normal case; a literal value counts as 1 unless it is an omitted argument. Hidden and filtered-out rows are counted, since COUNTA has no visibility awareness. |
value2, ... | Optional | Up to 254 further ranges or values, counted together into a single total. They do not have to be adjacent, the same size, or on the same sheet. |
More worked examples
B2:B9 holds three customer names, two order dates, one #N/A left by a failed lookup, the logical value TRUE, and one genuinely empty cell.
=COUNTA(B2:B9)Returns: 7
Everything except the empty cell counts, errors and logicals included. =COUNT(B2:B9) on the same range returns 2 - only the two dates register as numbers.
C2:C21 is filled with =IF(B2="", "", B2), and 14 of the 20 source rows actually have a value.
=SUMPRODUCT(--(C2:C21<>""))Returns: 14, where =COUNTA(C2:C21) on the same range returns 20.
Every cell in C holds a formula, so COUNTA counts all twenty. The comparison against "" is what distinguishes a real result from a formula-produced blank.
A checklist occupies the 30 rows D2:D31, and 18 of them have been filled in by hand.
=COUNTA(D2:D31)/ROWS(D2:D31)Returns: 0.6, which formatted as a percentage reads 60%.
ROWS keeps the denominator correct if the range is later extended. If the checklist column contains formula-blanks rather than typed entries, swap COUNTA for the SUMPRODUCT form above.
Common mistakes
- Formula-produced empty strings are counted
- A column of =IF(A2="", "", A2) looks half empty and counts as completely full, because each cell holds a formula. The COUNTA total then quietly overstates every headcount and completion rate built on it. Use =SUMPRODUCT(--(A2:A100<>"")) when the range contains formulas, and reserve COUNTA for ranges people type into.
- A single typed space counts as content
- Pressing space in a cell to clear it leaves a text value one character long. COUNTA counts it, ISBLANK reports FALSE, and lookups against it fail. Find them with =SUMPRODUCT(--(TRIM(A2:A100)=""))-COUNTBLANK(A2:A100), and clear them properly with Delete rather than the space bar.
- COUNTA plus COUNTBLANK can exceed the size of the range
- COUNTBLANK treats a formula returning "" as blank, while COUNTA treats it as content, so both count the same cell. Over a 100-cell range with 20 formula-blanks the two totals add up to 120. Do not use one as the complement of the other; derive the second from =ROWS(range)-COUNTA(range) if you need them to agree.
- Whole-column references include the header
- =COUNTA(A:A) on a table with a header row returns one more than the number of data entries, and any blank spacer rows or footnotes further down are counted too. Point at the data range, =COUNTA(A2:A1000), or use a structured reference such as =COUNTA(Table1[Customer]).
- Filtered and hidden rows still count
- COUNTA has no awareness of what is visible, so filtering a table down to twelve rows leaves the total unchanged. For a count that follows the filter, use =SUBTOTAL(103, A2:A100), which ignores rows hidden by a filter, or =AGGREGATE(3, 5, A2:A100), which also ignores manually hidden rows.
Frequently Asked Questions
COUNT counts numbers and dates only. COUNTA counts anything that is not an empty cell, including text, logicals and errors. COUNTBLANK counts empty cells, and also counts cells holding a formula that returns an empty string. On a column of names, COUNT gives 0 and COUNTA gives the headcount; on a column of formulas, COUNTA and COUNTBLANK will both count the same cells.
Almost always one of three things: formulas returning "", cells cleared with the space bar rather than Delete, or a whole-column reference picking up the header. Test with =COUNTA(A2:A100)-SUMPRODUCT(--(TRIM(A2:A100)<>"")) - a non-zero answer tells you how many of the counted cells hold nothing visible.
Yes. Error values are content as far as COUNTA is concerned, so a lookup column full of #N/A reports as fully populated. If errors should not count, either fix them at source with IFERROR or count with =SUMPRODUCT(--NOT(ISERROR(A2:A100))*--(A2:A100<>"")).
Use =SUMPRODUCT(--(A2:A100<>"")). It counts only cells whose value is not a zero-length string, which excludes the formula-blanks COUNTA includes. The one caveat is that it propagates any error in the range, so wrap the comparison in IFERROR if the column can contain #N/A.
Yes - it accepts up to 255 arguments, so =COUNTA(B2:B50, E2:E50, Sheet2!A2:A50) returns a single combined total across three ranges that need not be adjacent, the same size, or even on the same sheet. Overlapping ranges are counted twice, so keep them disjoint.
Related Tools
COUNTIF Generator
Count only the cells that meet a condition, which COUNTA cannot do.
COUNTIFS Generator
Count rows that satisfy several conditions at once.
Count Unique Values
Count distinct entries rather than every non-empty cell.
SUBTOTAL Generator
Get a count that follows the filter instead of ignoring it.