COUNTA Excel vs COUNT: Which Cells Do They Count in a Range?

In Excel, COUNT counts numbers in a range, while COUNTA counts cells that contain something, including text, numbers, logical values, and errors. Use COUNT for a numeric tally, such as how many sales amounts are entered; use COUNTA to count populated cells, such as how many names or responses have been supplied.

The quick difference between COUNT and COUNTA

The function names are similar, but their definitions are not interchangeable. COUNT asks whether a referenced cell contains a numeric value. COUNTA asks whether the cell is nonempty. As a result, a text label can be included by COUNTA and skipped by COUNT, while a number is included by both.

COUNT: counts numbers, including dates and times stored as numbers. It ignores text and genuinely empty cells in a referenced range.
COUNTA: counts cells containing data of any type, including text, numbers, dates, logical values, and error values. It ignores genuinely empty cells.
Both: return a count, not a sum. Neither function adds the numeric values in the cells.

For example, a list with three numbers and two text entries produces a COUNT of 3 and a COUNTA of 5, assuming the other cells in the range are truly empty. The right function therefore depends on what you mean by “count”: count numeric measurements, or count entries of any kind.

How the functions work in a worksheet

Both functions take one or more values or cell references. In everyday worksheets, the most useful form is a range reference: select the cells to inspect, and Excel evaluates each cell according to the function’s rules. A range may include blank cells; those blanks do not increase either result.

COUNT recognizes numeric values. This includes ordinary numbers, zero, negative numbers, percentages, and dates or times that Excel stores as numeric serial values. A displayed date may look like text to a person, but if Excel stores it as a valid date value, COUNT includes it. A number-looking entry stored as text, on the other hand, is generally ignored when it is inside the referenced range.

COUNTA recognizes cell contents rather than just numeric values. It includes text such as a person’s name, a TRUE or FALSE value, an error such as #N/A, and a formula result—even if that result looks blank. It does not count a genuinely unused cell. This distinction explains why COUNTA is useful for tracking filled-in records, but can sometimes report a larger count than the number of visibly nonblank cells.

In Excel for Microsoft 365 on Windows, enter a function in a worksheet cell by typing an equals sign followed by the function name and its range, then press Enter. You can also select the destination cell and use the formula bar. The function syntax and counting rules are the same in Excel for the web and Excel for Mac, although ribbon layout and some interface details can differ.

A worked example with numbers, text, and blanks

Suppose A2:A8 contains 12 in A2, the text Pending in A3, a genuinely empty A4, a formula returning an empty string in A5, a valid date in A6, TRUE in A7, and an #N/A error in A8. The date is a numeric Excel date, and the formula in A5 displays as blank. This layout illustrates why the functions can return different results.

With that layout in A2:A8, enter this in a result cell to count numeric values:

=COUNT(A2:A8)

The result is 2: COUNT includes 12 and the date, but not the text, the empty cell, the empty-string formula result, TRUE, or the error. Excel stores the date as a number, which is why it contributes to the count.

With the same A2:A8 layout, enter this in another result cell to count cells containing something:

=COUNTA(A2:A8)

The result is 6: COUNTA includes A2, A3, A5, A6, A7, and A8. It excludes only A4, the genuinely empty cell. The formula in A5 counts even though it displays no visible characters. The error in A8 also counts; COUNTA does not evaluate whether a cell’s contents are valid or useful for your purpose.

To create the empty-looking formula in A5 for this example, assume A5 is the target cell and enter:

=""

This formula returns a zero-length text string. It is not the same as clearing the cell: COUNTA treats the formula cell as occupied. If you need to check whether cells are visually blank, inspect whether formulas or spaces are present rather than relying on appearance alone.

What counts as a number, text, or blank

Some of the most confusing results come from values that look alike on screen but have different underlying types. A cell containing the numeric value 450 and a cell containing the text “450” may display identically. COUNT includes the numeric value and ignores the text value when both are in a referenced range. COUNTA includes both because neither cell is genuinely empty.

Formatting does not usually change the underlying value. Applying a date format to a number can make it look like a date, but COUNT is responding to the stored numeric value, not the display format. Likewise, a number formatted as a percentage remains numeric and is counted. If a value was imported as text, changing its number format alone may not convert it to a number; data conversion may be needed before COUNT gives the result you expect.

“Blank” also has more than one practical meaning in Excel. A truly empty cell has no entry. A cell containing a space is not empty, and COUNTA counts it. A formula that returns an empty string is also not truly empty, so COUNTA counts it even though the grid appears blank. Cells containing errors are not blank either. These distinctions matter in forms, imported data, and worksheets where formulas fill a large area in advance.

How to choose the right function

Start by defining the unit you want to count. If each valid record must have a numeric amount, use COUNT on the amount column. If each record is identified by a name or text response, COUNTA may better measure how many entries have been supplied. If rows need several fields to qualify as complete, neither function alone defines “complete”; you need a rule that checks the required columns.

Use COUNT to count numeric observations, such as entered quantities, scores, dates, or invoice amounts.
Use COUNTA to count filled-in cells where any content is acceptable, such as names, notes, IDs stored as text, or survey answers.
Use a different function or a more specific test when you need to count cells meeting a condition, distinguish errors, or count only rows with several required values.

Consider a survey column where respondents may enter either a number or a written answer. COUNT would miss written answers, so COUNTA is a more suitable first count of responses. However, if the column contains formulas prefilled down hundreds of rows and those formulas return empty strings, COUNTA will include those formula cells too. In that situation, the data setup or the counting rule must be adjusted to reflect what qualifies as a real response.

Common problems and how to diagnose them

COUNTA is higher than the visible entries. Look for formulas returning an empty string, spaces, hidden characters, or errors. A cell can look blank without being empty. Select a suspicious cell and inspect the formula bar; clearing the cell is different from leaving a formula that returns no visible text.

COUNT is lower than expected. Some entries may be numbers stored as text, especially after copying data from a website, CSV file, or another system. Check a sample cell and see whether Excel recognizes it as numeric. Converting the imported values to numbers can make them eligible for COUNT. Do not assume that changing the display format alone changes the stored type.

A date is counted by COUNT. This is expected when Excel recognizes it as a date. Dates and times are stored as numbers, so COUNT includes them. If you intend to count only amounts and exclude dates, use a range containing only the amount field or define a more specific condition.

A formula cell seems blank but still counts. If a formula returns an empty string, COUNTA includes the cell. A truly empty cell and a formula result of empty text are different states. If the distinction matters, use a method designed around the actual criteria—for example, count source data before formulas are filled in, or test the source field rather than the display cell.

The count includes errors. COUNTA counts an error value because the cell contains a value, even if that value represents a calculation problem. If the aim is to count valid records only, decide how errors should be handled and use an appropriate condition or clean the underlying data rather than interpreting COUNTA as a validity check.

Related functions and useful alternatives

COUNT and COUNTA are general-purpose counts; they do not filter by a condition. Excel’s related functions can be more suitable when the question is narrower. COUNTBLANK counts blank cells, including cells whose formulas return an empty string. COUNTIF counts cells in a range that meet one criterion, while COUNTIFS can apply multiple criteria. These functions answer different questions, so choose based on the rule you want to apply rather than substituting one for another.

For example, a worksheet might need to count orders with a particular status, or count numeric amounts above a threshold. That is a criteria-based task, not simply a count of all numbers or all occupied cells. In Microsoft 365, functions such as FILTER can also return matching records for further inspection, but they are not necessary for a straightforward count. COUNT and COUNTA remain useful when the goal is simply to summarize what types of entries appear in a range.

When counting records, also consider whether you mean cells or rows. COUNTA over a name column counts nonempty name cells, not necessarily unique people or complete records. Repeated names count more than once, and a row missing a required field may still contribute one entry. A precise question—such as “How many nonblank order IDs?” or “How many rows have both an ID and an amount?”—helps determine the right function and range.

Frequently Asked Questions

Does COUNTA count cells with formulas that show blank?
Yes. If a formula returns an empty string, the cell still contains a formula result, so COUNTA includes it. A cell that has been cleared and is genuinely empty is not counted.

Does COUNT include dates and times?
Yes, when Excel recognizes the entries as dates or times. Excel stores them as numeric values, so COUNT includes them in a referenced range. A date-like string stored as text may be ignored instead.

Does COUNT count numbers stored as text?
Not when they are text entries inside the referenced range. A cell displaying 450 may contain the number 450 or the text “450”; COUNT includes the former and skips the latter. COUNTA includes either because both cells contain something.

Does COUNTA count zero or errors?
Yes. Zero is a numeric value, and an error such as #N/A occupies a cell. Both functions count zero; COUNTA also counts the error, while COUNT does not count an error in a referenced range as a number.

Which function counts nonblank cells?
COUNTA is the standard choice for cells that contain any kind of content. Remember that formulas returning empty strings and cells containing spaces still count, so inspect the range if the result is larger than the number of visibly filled cells.

Can COUNT and COUNTA count more than one range?
Yes. Both functions can take multiple arguments, including separate ranges. Their rules remain the same for each referenced range: COUNT includes numeric values, while COUNTA includes cells that are not genuinely empty. Check for overlapping ranges if a cell might otherwise be included more than once.

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.