The Excel COUNTA function, explained interactively.
COUNTA counts every non-blank cell in its arguments — numbers, text, booleans, errors, and formula-"" results all count. Only truly empty cells are skipped. The natural tool for “how many rows exist” questions where the column contains mixed types — survey responses, audit logs, task lists.
How to use COUNTA
- Type
=COUNTA(and select the range — a whole column (B:B), a bounded block (B2:B100), or multiple ranges separated by commas. - Close the parenthesis. COUNTA returns a single integer — the tally of non-blank cells.
- To diagnose hidden formula-blanks, put
=COUNTA(B2:B100) - COUNTIF(B2:B100, "")in an adjacent cell. The gap is how many cells contain a formula returning"". - For the numeric-only tally, reach for COUNT. For conditional counts, COUNTIF / COUNTIFS.
In the demo below, every non-blank cell gets a ✓ in the Type column — including the text-stored date and the boolean. Only the single truly-blank row is skipped. Compare this screen with the COUNT demo (same data, numerics-only) to see the filter difference.
"" alike. Only truly empty cells are skipped. The natural choice when you want “how many rows exist”.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Field | Value | Type | Count |
| 2 | Score | 92 | number ✓ | 7 |
| 3 | Score | 87.5 | number ✓ | |
| 4 | Submitted | 2026-04-15 | text ✓ | |
| 5 | Passed? | TRUE | bool ✓ | |
| 6 | Notes | Great | text ✓ | |
| 7 | Score | (blank) | blank | |
| 8 | Score | 74 | number ✓ | |
| 9 | Review | N/A | text ✓ |
=COUNTA(B2:B9). COUNTA returns 7 — every cell except the one truly blank row. Compare with COUNT, which returns 3 (numerics only). The gap of 4 tells you how many rows contain text, booleans, or text-stored dates — exactly the diagnostic COUNTA is best at.COUNTA syntax and arguments
Variadic — one required argument, up to 254 optional. See Microsoft’s official COUNTA reference for the canonical specification.
=""). Only truly empty cells are skipped. Up to 255 arguments total; arrays and 2D blocks are flattened.COUNTA examples
Four patterns that cover most real-world COUNTA use.
Example 1: COUNTA — response rate on a survey
The canonical case. Count how many rows filled in a column regardless of data type — numbers, text, yes/no alike.
Returns 87 if 87 of 99 rows responded. Divide by ROWS(B2:B100) for a response-rate percentage. COUNTA is blind to content type — a rating of “5”, a text comment “Great”, and a boolean TRUE all count equally.
Example 2: COUNTA + COUNT — data-quality diagnostic
Compare COUNTA with COUNT to reveal how many cells contain non-numeric values in a column that should be numeric.
A non-zero result flags cells with text, booleans, text-stored dates, or formula-"" hiding in what should be a clean numeric column. Works as a live data-quality indicator on dashboards — if it goes above zero, someone’s entered a placeholder or a typo.
Example 3: COUNTA — dynamic last-row detection
Use COUNTA to find the row count of a growing table, then feed it into OFFSET, INDEX, or array formulas that need a dynamic range size.
Returns a range from B2 down to the last non-blank row of column B (minus 1 for the header). As rows are added, the range expands automatically. Converting to a named Table (Ctrl+T) is the modern-Excel equivalent; the COUNTA-OFFSET pattern remains useful for backwards-compat workbooks.
Example 4: COUNTA across sheets
Like other aggregate functions, COUNTA accepts multiple ranges — including cross-sheet references. Handy for rolling up monthly tabs.
Sums the non-blank counts across three monthly sheets. The - 3 removes the header row from each sheet. For sheets in a known order, =COUNTA(Jan:Mar!B2:B100) uses a 3D reference and needs no header correction.
Common COUNTA errors and fixes
Four failure modes, each with what to check and how to recover.
COUNTA counts cells that look blank
Cause: the cells contain a formula returning "" — an empty string that looks blank to the eye but is non-blank to COUNTA. The single most common COUNTA surprise.
Diagnose with =COUNTA(range) - COUNTIF(range, ""). Fix upstream by rewriting IF(A2="", "", …) to IF(A2="", , …) (bare comma returns a true blank). Or switch to COUNTIF(range, "<>") — it counts truly-non-blank without the empty-string wrinkle.
COUNTA over-counts the header row
Cause: the range includes row 1 (B:B or B1:B100) instead of starting at the first data row.
Bound the range explicitly from the first data row: =COUNTA(B2:B100). Or subtract 1 for each header row in the range. Convert to a Table (Ctrl+T) so you can reference Table1[column] which never includes the header.
COUNTA includes error cells
Cause: COUNTA counts #DIV/0!, #N/A, and every other error value as non-blank. An error cell is still a value.
For error-free counts, use =COUNT(range) (skips errors along with text), or filter explicitly: =SUMPRODUCT(--NOT(ISERROR(range))). The-- coerces the boolean array to 1/0 for SUMPRODUCT to total.
COUNTA mismatched on shared vs exported data
Cause: CSV exports and some paste operations preserve non-breaking spaces (CHAR(160)) in cells that look empty. COUNTA counts them; the eye misses them.
Find-and-replace non-breaking spaces with nothing: Ctrl+H, enter CHAR(160) via Alt+0160, leave the replacement blank. Or wrap column reads with TRIM — real Excel TRIM doesn’t strip NBSP, but CLEAN does remove some invisible chars.
COUNTA vs COUNT, COUNTIF & COUNTBLANK
Four counters, four different definitions of “cells worth counting”. Pick by what you’re measuring.
| Function | Counts | Conditional? | Typical use |
|---|---|---|---|
| COUNTA | Any non-blank cell | No | Row-exists count; survey responses; dynamic last-row |
| COUNT | Numeric cells only | No | Tally numeric responses; diagnose text-as-number bugs |
| COUNTIF | Cells matching a criterion | One condition | How many rows equal “East”, > 100, “<>” |
| COUNTBLANK | Empty cells only | No | Find missing data in a form / survey column |
Rule of thumb: COUNTA when content type doesn’t matter — you just want to know the row is populated. COUNT when numeric cleanliness matters. COUNTIF / COUNTIFS when the count is filtered. COUNTBLANK is COUNTA’s complement — useful for finding gaps in required fields.
COUNTA frequently asked questions
6.01COUNTA vs COUNT: what’s the difference?▸
COUNTA counts every non-blank cell regardless of type — numbers, text, booleans, errors. COUNT counts only numeric cells and skips the rest. Use COUNTA when you want to know how many rows have any value (how many people responded). Use COUNT when you specifically need a numeric tally (how many scores were entered).
6.02Does COUNTA count cells with a formula returning an empty string?▸
Yes — this is the main COUNTA gotcha. A formula like =IF(A2="", "", A2) returns an empty string when A2 is blank, and COUNTA counts that empty-string result as non-blank. The cell looks empty but inflates your count. Rewrite upstream formulas to return a true blank — use bare IF(…, …, ) with no quotes instead of "".
6.03How do I count only rows with real data using COUNTA?▸
If formula-blanks are inflating your count, combine COUNTA with COUNTIF(range, "") to subtract the empty-string cells: =COUNTA(B2:B100) - COUNTIF(B2:B100, ""). Or switch to COUNTIF(range, "<>") for a single formula that counts truly-non-blank cells directly. The SUMPRODUCT+NOT+ISBLANK idiom is the long-form equivalent.
6.04Can COUNTA count cells in multiple sheets?▸
Yes — pass multiple range arguments across sheets: =COUNTA(Jan!B:B, Feb!B:B, Mar!B:B). Or use 3D references where supported: =COUNTA(Jan:Dec!B2:B100) totals non-blank cells in that range across every sheet from Jan to Dec. The 3D form requires the sheets to be in order and breaks if tabs are reordered.
6.05Why is COUNTA returning a bigger number than I expected?▸
Three common causes: (1) hidden formula-blanks returning "" look empty but count; (2) trailing spaces or invisible characters in cells that look blank but aren’t; (3) the range extends beyond your data into populated footer rows. Use Go To Special → Blanks on the range to find cells that look empty to the eye but aren’t blank to COUNTA.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.