COUNTA in Excel: What It Counts and How to Use It

In Excel, COUNTA counts cells that are not empty, including cells with text, numbers, dates, logical values, errors, or formulas that return an empty text string. Enter a formula such as =COUNTA(A2:A20) to count nonempty cells in A2 through A20; genuinely blank cells are not counted.

How COUNTA works

COUNTA is a worksheet function for counting entries, rather than adding or evaluating their values. It checks each cell or value you supply and returns the number that contains something Excel recognizes as information. The contents do not need to be the same kind of data: a name, a number, a date, TRUE, and an error value can all contribute one to the result.

For example, suppose A2:A8 contains the name Maya, the number 0, a formula whose result is empty text, a genuinely blank cell, a date, a cell containing one space, and an error value. The range has six nonempty cells: every listed item except the genuinely blank cell. The space may look blank on screen, but it is a character; the formula result and error are also cell contents as far as COUNTA is concerned.

COUNTA does not decide whether an entry is useful, valid, unique, or visible. It simply counts nonempty values. If a column contains a title, notes, accidental spaces, or formula results that look blank, those may affect the total. That simple rule is powerful for tracking filled-in fields, but it also explains why the result can differ from what a quick visual scan suggests.

Syntax and arguments

The syntax is COUNTA(value1, [value2], ...). The first argument is required, and additional arguments are optional. An argument can be a cell reference, a range, or a value supplied directly to the function. In Excel, the function accepts up to 255 arguments. Most everyday formulas use one or more ranges.

With names in A2:A20, this formula counts the nonempty cells in that range and returns a whole number:

=COUNTA(A2:A20)

You can also count separate areas in one formula. With names in A2:A10 and email addresses in C2:C10, this counts filled cells in both ranges together; if there are six names and eight email addresses, the result is 14:

=COUNTA(A2:A10,C2:C10)

The ranges do not have to be the same size. COUNTA adds the count for each supplied argument, so check that separate ranges do not overlap if you do not intend to count an overlapping cell twice. In Microsoft 365 for Windows, the formula works in the same way as it does in current Excel for Mac and Excel for the web. Some regional Excel settings use semicolons rather than commas between arguments; use the separator required by your installation.

Practical examples

Here are common ways to use COUNTA. Each result depends on the stated cell layout, so the examples make clear what is being counted.

Count filled responses: With survey answers in B2:B51 and 37 respondents having entered an answer, =COUNTA(B2:B51) returns 37. If some cells contain formulas returning empty text, those are counted too, so use a layout where response cells are entered directly or account for that behavior.
Count several fields together: With first names in A2:A11 and last names in B2:B11, where nine first-name cells and eight last-name cells are nonempty, =COUNTA(A2:A11,B2:B11) returns 17. This is a count of filled cells, not a count of people; a person with both fields filled contributes two.
Count entries in an Excel table: If a table is named Table1, its column header is Status, and seven cells in that column contain values, =COUNTA(Table1[Status]) returns 7. A structured reference expands as table rows are added, which can make a maintained list easier to summarize than a fixed range.
Count cells that look empty as well as filled ones: With data in A2:A8 as described above, the formula =COUNTA(A2:A8) returns 6. That total includes the zero, date, space, formula result, and error, but not the genuinely blank cell.

When you build a workbook, put the result formula outside the range it counts. For instance, placing a count formula in A1 while counting A:A can create a circular reference, because the formula would be included in its own calculation. A bounded data range or a table column avoids that problem and makes the intended scope clearer.

What COUNTA counts as nonempty

Excel distinguishes a truly empty cell from a cell that contains a value that happens to appear blank. COUNTA counts text, numbers, dates, times, TRUE or FALSE, errors, and formulas. Dates and times are stored as numeric values in Excel, but they are counted by COUNTA for the same basic reason as other populated cells: the cell is not empty.

A particularly common surprise is a formula that returns an empty text string. It may display nothing, but the cell still contains a formula, so COUNTA counts it. A cell with one or more spaces is counted as well. So are cells containing a zero; a zero can be visually hidden by formatting, but the stored value remains present. If you are auditing data entry, these distinctions matter: a count of populated cells does not necessarily equal a count of visible labels or meaningful answers.

To check suspicious entries, select a cell and look at the formula bar. A cell that appears blank may contain spaces or a formula. You can also test a specific cell with ISBLANK when investigating whether it is genuinely empty. Do not treat a display format as proof that a cell has no content: custom number formats can hide displayed values without removing them.

COUNTA compared with COUNT and COUNTBLANK

COUNTA is often confused with COUNT. COUNT includes numeric values, including dates and times, but does not count ordinary text. COUNTA counts both numeric and text entries, along with other nonempty content. With the example range A2:A8 containing a zero, a date, and four other nonempty items, COUNT returns 2 while COUNTA returns 6.

With the same A2:A8 layout, where A5 is genuinely blank and A4 contains a formula returning empty text, =COUNTBLANK(A2:A8) returns 2. COUNTBLANK counts empty cells and also treats formulas that return empty text as blank for its count. That means COUNTBLANK and COUNTA do not always divide a range into two perfectly opposite groups: a formula returning empty text is counted by both functions under their respective rules.

Choose the function based on the question you are asking:

Use COUNTA to count cells that contain something, regardless of data type.
Use COUNT to count numeric values only.
Use COUNTBLANK to count cells Excel treats as blank, including formula results that are empty text.

If you need to count entries meeting a condition, rather than all nonempty cells, a conditional counting function such as COUNTIF may be more suitable. For example, counting only cells that contain a particular status is a different task from counting every cell that has content. Select a function according to the meaning of the result you need, not merely because the column looks populated.

Counting visible rows and filtered lists

COUNTA counts nonempty cells even when their rows are hidden or excluded by a filter. This is often correct when you want the total number of entries in the full data set. It is not correct if your question is specifically how many nonempty entries are currently visible after filtering.

For a filtered list, SUBTOTAL can count visible nonempty cells. With records in A2:A10, a header outside that range, and three nonempty records currently visible, =SUBTOTAL(103,A2:A10) returns 3. The function number 103 applies the COUNTA operation while excluding filtered-out rows and manually hidden rows. Function number 3 also performs a COUNTA-style count but treats manually hidden rows differently, so 103 is generally the more explicit choice when the displayed rows are what you want to count.

Keep the range limited to the data rows so that a header is not included, and remember that changing the filter can change the SUBTOTAL result. If you want a stable count of every populated record regardless of visibility, use COUNTA instead. These formulas answer different questions rather than one being universally better.

Common problems and ways to check the result

If COUNTA returns a larger number than expected, inspect cells that look empty first. Extra spaces, formulas that return empty text, hidden values, and error cells all count. If it returns too small a number, check whether the formula range covers all intended rows and whether the data is actually in the cells you expect. A range ending at row 100 will not include a new entry in row 101.

Several practical checks can make a count more trustworthy:

Confirm the first and last cell addresses in the formula bar or by selecting the range.
Check visually blank cells for formulas or spaces before deciding they are empty.
Decide whether hidden and filtered-out rows should be included; use COUNTA or SUBTOTAL accordingly.
Keep headers and notes outside the data range, or deliberately include and adjust for them.
Use a table column when your list is expected to grow, so its structured reference follows added rows.

A related misconception is that COUNTA counts only text. It does not: numbers, dates, errors, and logical values are counted too. Another is that it counts the number of rows containing data. It counts cells, so a row with three filled fields contributes three to a range spanning those fields. For a record count, count one dependable field per record, such as a required ID column, rather than counting every column together.

Frequently Asked Questions

Does COUNTA count a formula that displays nothing?

Yes. A cell containing a formula is nonempty even when the formula returns an empty text string and the cell looks blank. If you need to exclude those results, choose a method suited to your data and verify how it treats formula-generated blanks.

Does COUNTA count zero?

Yes. Zero is a numeric value, not an empty cell. COUNTA counts it even if a number format or worksheet setting makes the zero difficult to see.

Does COUNTA include hidden or filtered rows?

Yes. COUNTA counts nonempty cells whether or not their rows are visible. For a filtered list where only visible nonempty cells should count, use SUBTOTAL with function number 103.

Can COUNTA count more than one range?

Yes. Add each range as another argument, separated by commas in standard English-language Excel syntax. Check for overlapping ranges, because the same cell can be counted more than once if it appears in multiple arguments.

Why is COUNTA different from the number of records?

COUNTA counts populated cells, not records. If the range includes several columns, one record can contribute multiple cells; headers and notes may contribute too. Count a single consistently populated field, such as an ID column, when you need a record total.

Choosing the right count

Use COUNTA when the goal is a straightforward count of cells that contain something, and choose the range to match the question. For a quick tally of filled fields, it is simple and reliable once you account for formulas, spaces, errors, and hidden rows. For numeric-only totals, use COUNT; for blank cells, use COUNTBLANK; and for visible filtered records, use SUBTOTAL. Knowing what Excel considers nonempty turns COUNTA from a convenient shortcut into a count you can interpret correctly.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.