Statistical

COUNTA Function in Excel

Counts the number of cells that are not empty.

Syntax

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

Arguments

  • value1 (required): First value or range to count
  • value2 (optional): Additional values or ranges

Examples

  • =COUNTA(A1:A100) - Count non-empty cells - Result: Non-empty count
  • =COUNTA(A:A)-1 - Count data rows (excluding header) - Result: Data row count

Excel COUNTA function documentation

  • COUNTA counts all non-empty cells in a range — text, numbers, errors, and formulas returning any value including "".
  • Syntax: =COUNTA(value1, [value2], …). Available in all modern Excel versions.
  • Use for form completion rates, checklist audits, and "how many rows have data" metrics.
  • Related: [COUNT](/functions/count/), [COUNTBLANK](/functions/countblank/), [COUNTIF](/functions/countif/).

What COUNTA treats as non-blank

  • Text labels, numbers, dates, and error values (#N/A) all increment COUNTA.
  • A formula returning "" (empty text) is still non-blank — COUNTA counts it.
  • A truly empty cell with no formula is not counted.
  • Spaces entered manually count as non-blank text unless the cell appears empty visually.

Step-by-step: form completion percentage

  • Step 1 — Required field entries in column E rows 2–101 (100 forms).
  • Step 2 — Filled count: =COUNTA(E2:E101).
  • Step 3 — Completion rate: =COUNTA(E2:E101)/100 formatted as percentage.
  • Step 4 — Missing count: =COUNTBLANK(E2:E101) only if cells are truly empty, not "" formulas.
  • Step 5 — For "count if region filled AND amount filled" use [COUNTIFS](/functions/countifs/).

COUNTA vs COUNT vs COUNTBLANK audit

  • COUNTA + COUNTBLANK rarely equals total rows when formulas return "".
  • Use ISBLANK or LEN(TRIM(cell))=0 helpers to find pseudo-blank cells.
  • COUNT counts numeric cells only — compare COUNTA-COUNT for text-heavy columns.
  • Problem: [COUNTIF not counting text](/problems/countif-not-counting-text/) for criteria issues.

Worked examples to copy

  • Rows with any data: =COUNTA(A2:A1000).
  • Multi-column fill check: =COUNTA(B2:D2) on one row for partial completion.
  • Exclude header: =COUNTA(A:A)-1 when column has one title row.
  • With Table: =COUNTA(Survey[Email]) for dynamic row growth.

People also ask

  • Does COUNTA count spaces? — Yes, a cell with only a space is non-blank.
  • COUNTA and empty text formulas? — Formula ="" counts as non-blank for COUNTA.
  • COUNTA vs COUNTIF? — COUNTA counts all non-empty; COUNTIF counts by criteria.
  • Why COUNTA too high? — Hidden spaces, zero-width characters, or "" formulas.

Common errors

  • Counts cells with formulas returning empty string
  • Counts error values

Use cases

  • Data completeness
  • Dynamic range sizing
  • Row counting

Frequently asked questions

  • What does COUNTA count? COUNTA counts all non-empty cells: numbers, text, logical values, errors, and formulas returning values. Only truly empty cells are not counted.
  • Does COUNTA count cells with formulas returning blank? It depends. =COUNTA counts cells with ="" (formula returning empty string). Truly empty cells (no formula) are not counted. This can cause unexpected results.
  • How do I count rows of data? =COUNTA(A:A)-1 counts non-empty cells minus header. Or =COUNTA(A2:A1000) to count from row 2. For tables, use =ROWS(Table1) for exact count.
  • How do I create a dynamic range with COUNTA? Use with OFFSET or INDEX: =SUM(A1:INDEX(A:A,COUNTA(A:A))) sums from A1 to last non-empty cell. Or OFFSET: =SUM(OFFSET(A1,0,0,COUNTA(A:A),1)).
  • What is the difference between COUNTA and ROWS? COUNTA counts non-empty cells. ROWS counts total rows in a range regardless of content. =ROWS(A1:A100)=100 always, =COUNTA(A1:A100) varies with data.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use COUNTA

  • Data completeness — common Statistical scenario for COUNTA.
  • Dynamic range sizing — common Statistical scenario for COUNTA.
  • Row counting — common Statistical scenario for COUNTA.

COUNTA in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • COUNTA syntax: =COUNTA(value1, [value2], ...)
  • value1 (required): First value or range to count
  • value2 (optional): Additional values or ranges
  • Confirm COUNTA arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm COUNTA arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • COUNT (/functions/count/): Counts the number of cells that contain numbers.
  • COUNTBLANK (/functions/countblank/): Counts the number of empty cells in a specified range.
  • COUNTIF (/functions/countif/): Counts the number of cells that meet a criterion.
  • ROWS (/functions/rows/): Returns the number of rows in a reference or array.

Errors to watch for

  • Counts cells with formulas returning empty string — review causes on linked error pages in the directory.
  • Counts error values — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter COUNTA on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

COUNTA worked examples to copy

  • =COUNTA(A1:A100) — Count non-empty cells. Expected result: Non-empty count.
  • =COUNTA(A:A)-1 — Count data rows (excluding header). Expected result: Data row count.

COUNTA reference summary for crawlers and offline review

  • COUNTA belongs to the Statistical category in Excel. Counts the number of cells that are not empty.
  • Full syntax: =COUNTA(value1, [value2], ...). Open /functions/counta/ for parameters, FAQs, and related pages.
  • Common mistakes: Counts cells with formulas returning empty string; Counts error values
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.