COUNTA Function in Excel: Count Nonblank Cells Correctly

Formulas & functions · Updated November 3, 2026

To count nonblank cells in Excel, use COUNTA. With five entries and two truly empty cells in A2:A8, enter =COUNTA(A2:A8); the result is 5. COUNTA is useful for tallying filled-in names, responses, dates, or other values without needing to know what kind of value each cell contains. Before you start, identify the range you want to count and decide whether cells with formulas that display nothing should count as filled.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows. COUNTA is also available in Excel for Mac and Excel for the web; the function works the same way in those versions.
  • A worksheet range to check. The cells can contain text, numbers, dates, logical values, errors, or formulas.
  • A clear definition of what you consider filled. COUNTA counts formulas that return an empty-looking result and cells containing spaces, which may not match your intended definition of a completed entry.

Step-by-Step

  1. Choose what you want to count. Decide whether you need the number of cells containing something, or the number of cells containing a particular type of value. COUNTA answers the first question: it counts cells that are not truly empty. For example, it can count how many rows in a signup list have a name entered, even if other cells in those rows contain numbers or dates. If you need a count of numbers only, COUNT is the more relevant function; if you need to count entries matching a condition, use a conditional counting function instead.
  2. Identify the exact range. Note the first and last cells to include, such as A2 through A8. Decide whether the heading row should be excluded. If A1 contains a heading such as Name, including A1 will add one to the count, so a list with seven completed entries could appear to have eight. It is usually clearer to count the data rows and leave headings outside the range. You can count a single column, a rectangular block, or separate ranges.
  3. Select the cell where the answer should appear. Click an empty cell outside the range being counted, such as B2 when the entries are in column A. Putting the result inside the range can cause the formula to count itself, since the formula cell is no longer empty. If you want a total near a list, place it above or below the data and make sure the selected range does not include that total cell.
  4. Enter the basic formula. With five filled cells and two truly empty cells in A2:A8, type =COUNTA(A2:A8) in the result cell and press Enter. The result is 5. The function name is followed by parentheses containing the range reference. The colon means the range runs from A2 through A8, including every cell in between. You can also type the formula directly into the formula bar after selecting the destination cell.
  5. Check that the range is right. With the same example layout, the formula =COUNTA(A2:A8) should return 5. Compare that result with the worksheet, including the first and last rows you intended to count. If you expected a different number, inspect the cells that seem blank: a space, an apostrophe, or a formula that returns an empty-looking result can make a cell count as nonempty. Also check that the range does not include a heading, notes, or the result cell.
  6. Count separate ranges when needed. You can provide more than one range as arguments, separated by commas. With three filled cells in A2:A5 and two filled cells in C2:C5, enter =COUNTA(A2:A5,C2:C5); the result is 5. This is handy when relevant data sits in separate columns or sections. Check that the ranges do not overlap: if the same nonempty cell is included twice, it can be counted twice because it is supplied as two arguments.
  7. Use the count in a changing list. COUNTA recalculates when cells in its referenced range change, so the result can stay current as entries are added or cleared within that range. For example, if you expect up to 100 responses, reference the intended response cells rather than only the first few rows. A bounded range is often easier to understand and can avoid counting unrelated notes elsewhere on the worksheet. If the data is an Excel table, you can also use its data column as the range so the reference can expand with added table rows.

COUNTA can count text, numbers, dates, TRUE or FALSE values, errors, and formulas. It does not inspect a value to decide whether it is meaningful; it only checks whether the cell is empty. A cell containing zero is counted, as is a cell containing a date, because both contain values. A genuinely empty cell is not counted. This simple rule explains most unexpected results.

Common Mistakes

Counting a formula that displays nothing as blank. A cell containing a formula that returns an empty text string may look empty on the worksheet, but it still contains a formula. COUNTA counts that cell. If the goal is to count only visibly displayed entries, first decide how to treat formulas that return empty text; COUNTA alone does not make that distinction.

Overlooking spaces and invisible characters. A cell with one or more spaces is not truly empty. It counts even when the space is hard to see. Pasted data can also contain nonprinting characters, so a cell that appears blank may still hold content. Do not assume a visual blank will be ignored.

Including the header or total. If the range starts at the column heading, the heading counts. If it includes the result cell, that cell can count too once the formula has been entered. Keep labels and totals outside the range unless you intentionally want them included.

Using COUNTA when you need a narrower count. COUNTA includes text, numbers, errors, dates, and logical values. It does not mean “count names,” “count numeric answers,” or “count valid records.” If your worksheet contains mixed content, use a function or criteria-based approach designed for the specific type of entry you want to measure.

Assuming filters change the count. Filtering a list hides rows from view, but COUNTA still counts nonempty cells in the referenced range, including rows hidden by the filter. A manually hidden row is also included. If you need a count that responds to filtering, use a subtotal-based approach instead of expecting COUNTA to adapt.

Tips

  • Use a descriptive label beside the result, such as Completed responses, so readers know what the total represents. The label should be outside the counted range.
  • For a simple list, count only the entry column rather than a large block of worksheet cells. This reduces the chance of including notes, headings, or unrelated content.
  • When using a table, click or select the relevant data column to create a structured reference. Structured references are easier to interpret than long cell addresses and typically adjust as table rows are added or removed.
  • Remember that blank-looking is not the same as empty. To inspect a suspicious cell, select it and look at the formula bar; it may contain a space, a value, or a formula even if the grid looks blank.
  • Keep in mind that COUNTA counts each nonempty cell, not each unique item. If the same name appears three times in a range, all three cells contribute to the result.
  • If you copy the formula to another location, check whether Excel changed the range reference. Standard cell references usually adjust when copied; if you need a range to stay fixed, use absolute references by adding dollar signs to its row and column references.

For a filtered list, use SUBTOTAL with function number 103, which applies COUNTA-style counting while excluding filtered-out rows and manually hidden rows. With a list in A2:A20 and four nonempty entries currently visible, enter =SUBTOTAL(103,A2:A20); the result is 4. This is useful when the count should reflect only the rows currently shown. If you want manually hidden rows included but filtered-out rows excluded, function number 3 has different behavior; choose the option that matches how your worksheet is being used.

Troubleshooting

The result is higher than expected. Check whether the range includes a header, notes, or the destination cell. Then inspect cells that look empty for spaces or formulas returning empty text. You can select a suspicious cell and review its contents in the formula bar. If several cells may contain stray spaces, cleaning the source data is usually more reliable than changing the count formula to conceal the issue.

The result is lower than expected. Confirm that the range includes every row or column you mean to check. A common cause is stopping the range too early after new rows were added. Also verify that the supposed entries are actually in the cells you selected rather than in an adjacent column. A cell containing zero should count; if it does not, check the reference and whether the value is actually stored in another cell.

Excel shows an error instead of a number. Check the parentheses, range references, and separators. In standard English-language Excel syntax, separate multiple arguments with commas. Some regional settings use semicolons as list separators, so Excel may require the separator configured for your locale. The function name and the cell references should still be valid. If a reference points to a deleted or invalid range, correct it before evaluating the count.

The count does not change after editing the sheet. Press Enter after changing a formula or cell value, and confirm that the formula references the edited cell. Excel normally recalculates automatically; if the workbook is set to manual calculation, use Formulas > Calculation Options > Automatic. On Excel for Mac, the same calculation setting is available from the Formulas tab. Excel for the web generally recalculates formulas automatically.

The filtered count still includes rows you cannot see. That is expected with COUNTA. Replace it with the SUBTOTAL approach described above and verify that its range covers the list but not the heading. The number 103 is important when hidden rows should be excluded along with filtered-out rows. If the visible result still seems off, inspect the range for hidden nonempty cells or a header that has accidentally been included.

Frequently Asked Questions

What does COUNTA count in Excel?
It counts cells that contain any value or content, including text, numbers, dates, logical values, errors, and formulas. It ignores truly empty cells.

Does COUNTA count a formula that returns an empty string?
Yes. The cell contains a formula even if the displayed result looks blank, so COUNTA includes it. This is one of the most common reasons the result seems unexpectedly high.

Does COUNTA count cells containing zero?
Yes. Zero is a value, not an empty cell. A cell displaying 0 contributes one to the count.

Can COUNTA count multiple ranges?
Yes. Put each range inside the parentheses and separate the ranges with commas in standard Excel syntax. Avoid overlapping ranges unless counting a repeated cell more than once is intentional.

Does COUNTA ignore filtered or hidden rows?
No. COUNTA counts cells in its range even when their rows are hidden or filtered out. Use SUBTOTAL with function number 103 when the result should count only visible nonempty cells.

What is the difference between COUNT and COUNTA?
COUNT counts numeric values, while COUNTA counts cells containing any kind of content. Use COUNT for a numeric-only tally and COUNTA when text and other nonempty entries should also count.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.