The excel counta function is the fastest way to count any cell that isn't empty. Text. Numbers. Errors. Even those sneaky empty strings from a formula. If a cell has something in it, COUNTA counts it. That's the whole pitch.
Here's the thing — most people reach for COUNT first, then wonder why their text labels never get counted. COUNT only sees numbers. COUNTA sees everything. That single difference trips up so many spreadsheets that count function in excel threads on every forum end with the same fix: switch to COUNTA.
You'll use this one constantly. Attendance sheets. Survey responses. Project task trackers where the goal is "how many rows have data". Anywhere the question is "how many of these cells were filled in". Not optional. It's the workhorse counting function in microsoft excel, and the syntax is dead simple — but the pitfalls aren't.
This guide walks through the formula, what each argument does, how COUNTA differs from excel count function and COUNTBLANK, the empty-string gotcha that breaks audits, and seven real examples you can paste into a sheet today. Stick around for the SUBTOTAL trick — it's the one that makes filtered lists finally behave.
Short answer up front: =COUNTA(A2:A100). Range goes in. Number of non-empty cells comes out. Now let's unpack why that number sometimes lies.
Back in Excel 4, Microsoft realized COUNT was too restrictive for real spreadsheets. Most business data isn't purely numeric — there are names, status codes, dates formatted as text, region labels. COUNT skipped all of it. Users were writing nested IF formulas just to get a row count. COUNTA shipped in 1993 as the fix: count anything that isn't blank, regardless of type.
The "A" stands for "all" — it counts all non-empty values, not just numbers. You'll see the same naming convention with AVERAGEA and other "A" functions that treat text and logical values as part of the data set rather than ignoring them. Once you know that, the function family makes a lot more sense.
Today, COUNTA is built into every version of Excel on every platform — Windows, Mac, Excel Online, mobile. The behavior is identical across all of them. If you learn it once, it works everywhere. Same with excel online in your browser. No version-specific gotchas to memorize.
COUNTA returns the number of cells in a range that are not empty. That's it. A cell counts if it contains anything — text, a number, a date, a logical TRUE/FALSE, an error value, or a formula that produces any of those. A cell does NOT count only if it has never been touched or has been explicitly cleared (Delete key, not Backspace on a formula).
The function takes one or more arguments. The first is required. The rest are optional. Each argument can be a cell, a range, a named range, an array, or a literal value. COUNTA processes each one independently and adds the results together. No matter how many ranges you pass, you get back a single number.
Accountants reconciling line items. HR coordinators tracking who replied to the all-staff form. Project managers staring at task lists and trying to figure out how many are still open. Teachers checking grade entries. Anyone who has ever asked, "How many of these rows actually have data?" The excel countif crowd reaches for COUNTA daily — and so should you. It's one of those functions that earns its place on every cheat sheet, right next to SUM and VLOOKUP. Learn it once. Use it forever. The minutes you save every week add up fast over a career spent working in spreadsheets.
The counta function in excel doesn't care what's in a cell. Text? Counted. Number? Counted. #N/A error? Yep — counted. A formula that returns "" (an empty string)? Still counted, because the cell isn't actually empty — it holds a zero-length string. That last one is the source of nearly every "COUNTA is wrong" complaint. We'll fix it in the pitfall section below.
The formula is short: =COUNTA(value1, [value2], ...). value1 is required and accepts a single cell, a range, a named range, or even a literal value. Everything after that is optional — you can pass up to 255 arguments separated by commas.
Typed example: =COUNTA(A2:A100) returns the count of non-empty cells in column A from row 2 down to row 100. Type the formula into any cell, press Enter, and the number appears. That's it.
You can pass several disjoint ranges in one formula: =COUNTA(A2:A100, C2:C100, E2:E100). COUNTA totals the non-empty cells across all three. Useful when your data lives in non-contiguous columns — like a roster split across name, ID, and email columns.
Don't worry about overlap. If a cell is non-empty in two ranges you pass, COUNTA counts it twice. So pass ranges that don't overlap, or use a union range with the comma-inside-parens syntax: =COUNTA((A2:A100, C2:C100)).
You can mix ranges with hardcoded values: =COUNTA(A2:A10, "Pending", 5). That counts every non-empty cell in A2:A10 plus 2 (one for "Pending", one for 5). Weird but legal.
What about whole columns? =COUNTA(A:A) works and Excel handles it efficiently — no performance hit on modern versions. Saves you from updating the formula every time the list grows. Set it once, forget it.
Counts every non-empty cell — text, numbers, dates, errors, even formulas returning empty strings. Use when you want to count "anything filled in." The most permissive of the three counting functions.
Counts only cells with numeric values. Ignores text completely. Use when you specifically need numeric data points — like "how many test scores were recorded." Skips blanks and text labels.
The opposite of COUNTA. Counts truly empty cells AND cells with empty strings ("") — yes, both. Useful for finding gaps in your data. Pair with COUNTA to audit a column for completeness.
Counts cells matching one condition: <code>=COUNTIF(A:A, ">5")</code>. Use this when COUNTA is too broad. Combine with wildcards (<code>?*</code>) to count text-only cells while ignoring numbers and empty strings.
Forget the textbook. Here's how the counta function in excel shows up in actual sheets people maintain at work.
You've got names in column A and a checkmark column for today's date. To count who showed up, type =COUNTA(B2:B30) in cell B31. Every cell where someone marked attendance gets counted. Doesn't matter if you used "X", "✓", "present", or a number — non-empty is non-empty.
Want the no-show count? Subtract from the roster size: =COUNTA(A2:A30) - COUNTA(B2:B30). Two COUNTAs in one formula. Total enrolled minus total attended. Drop the result into the cell next to your attendance percentage and you've got a daily roll-up that updates as soon as someone checks in.
A survey has 200 respondents and 12 questions across columns B through M. To check how many people answered question 7: =COUNTA(H2:H201). Divide by 200 and you get a completion rate per question — instant view of which questions people skip. Format the result as a percentage and you're done.
Want to see all 12 completion rates side by side? Put COUNTA across row 202 dividing by 200, then conditional-format anything below 70% red. You'll spot the question wording problem in seconds. Beats reading every response one by one.
Column F holds the "Completed By" name for each task. Blank means open, filled means done. =COUNTA(F2:F500) gives you the closed-task count without any IF logic. Pair with excel count cells with text techniques if you only want tasks closed by certain people.
You inherited a 10,000-row product list. Some rows have SKUs, some don't — the previous owner stopped halfway. =COUNTA(B:B)-1 (subtract 1 for the header) tells you how many products actually have a SKU. Useful before you migrate to a new system.
Add a second COUNTA for the description column. Compare the two. If the SKU count is 8,200 and the description count is 9,400, you've got 1,200 products with descriptions but no SKU. Now you know exactly where to focus the cleanup work.
A form template uses column D for required fields. Before submission, you want to flag missing entries. =15-COUNTA(D2:D16) where 15 is the number of required fields. Returns the count of unfilled required cells. Bigger than zero? Form's not done.
Three sheets — Jan, Feb, Mar — each with sales reps in column A. Sum the rep counts across all three: =COUNTA(Jan!A2:A100, Feb!A2:A100, Mar!A2:A100). Single formula. Three sheets. One total. No helper cells, no manual addition, no risk of forgetting a sheet when you copy the formula to next quarter's report.
You're auditing a CSV import. Did all 25 columns get headers? =COUNTA(1:1). If the answer isn't 25, something's missing. Two-second sanity check before you build the pivot table.
You set up a dropdown list in column G using add drop down list in excel validation. Now you want to know how many cells have had a value selected versus how many are still pending. =COUNTA(G2:G500) handles it. The data validation rules don't affect COUNTA at all — it only cares whether a value was committed, not whether it matches the dropdown.
This is the bug that breaks 80% of COUNTA formulas. If a cell holds =IF(A2>5, "OK", ""), the cell looks blank on screen but contains an empty string. COUNTA counts it. ISBLANK returns FALSE. Conditional formatting treats it as filled. Your "completion rate" formula reads 100% when half the rows are visually empty.
The fix: use =COUNTIF(range,"?*") + COUNT(range). The ?* wildcard matches text of any length (at least one character), so empty strings get skipped. COUNT picks up the numbers. Together they replace COUNTA without the empty-string false positive.
Open any cell that shows up as blank but gets counted by COUNTA. If you see ="" or any IF formula with a "" branch, that's your culprit. The cell holds a zero-length string. Excel renders it as blank, but it's not blank in the strict sense — there's a value there.
Three ways to spot the offenders. First, click the cell and check the formula bar. If it shows an empty quote pair or a formula, the cell isn't blank. Second, use excel isblank function — =ISBLANK(A5) returns TRUE only for truly empty cells. False positives become obvious. Third, use Find & Replace (Ctrl+H) with regex enabled, find ^$, and Excel highlights every cell holding an empty string.
For exact non-empty counts that ignore empty strings, swap COUNTA for =SUMPRODUCT(--(LEN(A2:A100)>0)). LEN returns the character count of each cell's contents. An empty string has length 0. A truly blank cell also has length 0. SUMPRODUCT adds up the TRUE results. Same idea, no empty-string ghosts.
You can also build it with COUNTIF: =COUNTIF(A2:A100,"?*")+COUNT(A2:A100). The wildcard ?* means "at least one character of text", which kills empty strings. The +COUNT adds in any numeric cells the wildcard skipped. Two functions in one. Clean.
If you're auditing data entry and want to know every cell someone touched, COUNTA is correct. A user who typed a formula that returns "" still did something — they didn't leave the cell untouched. For that use case, COUNTA's behavior is exactly what you want. The bug only matters when "" is a placeholder for "no data" rather than "intentional blank."
Combine the two: =COUNTA(A2:A100)+COUNTBLANK(A2:A100) should equal the total cell count (99 in this case). If it doesn't, you've got something weird going on — maybe merged cells, maybe formula errors. The two functions are supposed to partition the range perfectly. When they don't, dig in.
This is the other classic COUNTA trap. Apply a filter to a column, type =COUNTA(A:A), and you'll still get the unfiltered total. COUNTA ignores filters completely — it sees every cell in the range, visible or not. To count only visible cells after a filter, switch to =SUBTOTAL(3, A2:A100). The 3 is the function number for COUNTA, and SUBTOTAL respects filter visibility. Use =SUBTOTAL(103, A2:A100) if you also want to ignore manually hidden rows.
COUNTA happily counts every #N/A, #DIV/0!, #REF!, and #VALUE! in your range. The cell isn't empty — it holds an error value — so COUNTA includes it. That's usually not what you want when you're trying to count "successful entries." To exclude errors, wrap with IFERROR or use =SUMPRODUCT((A2:A100<>"")*NOT(ISERROR(A2:A100))). Slightly more work, far more accurate.
When you merge cells, Excel only keeps the value in the top-left cell of the merged area. The other cells become truly empty. So =COUNTA(A1:A10) over a range containing one merged block of 3 cells returns 8, not 10. People expect it to return 10 because the merged cell looks like it has data across the whole span. It doesn't.
Apply AutoFilter to a column. COUNTA still counts everything — visible AND filtered out. To count only what's visible, use =SUBTOTAL(3, A2:A100). The 3 is COUNTA's function code. SUBTOTAL skips rows hidden by a filter automatically.
Need to ignore manually hidden rows too? Use code 103 instead of 3: =SUBTOTAL(103, A2:A100). The 100-series codes (101–111) ignore both filtered and manually hidden rows. This is the formula every accountant should bookmark.
COUNTA counts every non-empty cell, including duplicates. To count distinct values, Microsoft 365 users get the easy path: =COUNTA(UNIQUE(A2:A100)). UNIQUE strips duplicates first, then COUNTA tallies what's left.
On older Excel? Use =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) — the classic array trick. Each value gets weighted by 1/its_frequency, so duplicates sum to 1 each instead of inflating the count. Or jump to count unique values in excel for the full breakdown.
COUNTA only handles "non-empty". For "non-empty AND something else," reach for countifs function in excel: =COUNTIFS(A2:A100,"<>",B2:B100,"Active"). The <> criterion means "not blank" (it still has the empty-string bug, but cleaner than nesting COUNTAs).
For real conditional counting, COUNTIFS is the answer — multiple criteria, multiple ranges, all in one formula. Stack as many conditions as you need.
Once you're past "count the non-empty cells," the counta function in excel becomes a building block for bigger formulas. Here are the patterns that show up in serious spreadsheets.
You don't know how big your data set will be next month. Hardcoded ranges break. Use COUNTA to size them dynamically: =SUM(OFFSET(A2,0,0,COUNTA(A:A)-1,1)). The OFFSET function builds a range starting at A2, with the height determined by COUNTA. As you add rows, the SUM updates automatically. Old-school dynamic ranges, before tables existed.
For the last filled cell in a column: =INDEX(A:A, COUNTA(A:A)). Works when column A has no blank gaps. If there are gaps, use =LOOKUP(2, 1/(A:A<>""), A:A) instead — it finds the last non-empty cell even with holes. Useful for grabbing "latest entry" from a log.
You've got 50 tasks and want a completion bar. Tasks marked done go in column C. =COUNTA(C2:C51)/50 formatted as percentage gives the progress. Pair with conditional formatting and you've built a status dashboard in one cell.
A workbook with 12 monthly sheets, each tracking customer signups in column A. Total signups across the year: =SUMPRODUCT(COUNTA(INDIRECT("'"&{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}&"'!A2:A1000"))). Brutal, but it works without a single helper cell. The cleaner approach in Excel 365: =VSTACK(Jan:Dec!A2:A1000) wrapped in COUNTA. New tools, same idea.
Before calculating averages or ratios, wrap your division in an IF that checks COUNTA: =IF(COUNTA(B2:B100)=0, "No data", SUM(B2:B100)/COUNTA(B2:B100)). Prevents the dreaded #DIV/0! when the range is empty. A 5-second addition that saves a 30-minute debugging session.
You want to flag any row that has fewer than 5 columns filled. In column G: =IF(COUNTA(B2:F2)<5, "INCOMPLETE", "OK"). Counts the filled cells per row, flags rows below the threshold. Drop the formula down the column, sort by the flag, and you have an instant data-quality report.
Need a running count of non-empty cells down a column? In modern Excel, =SCAN(0, A2:A100, LAMBDA(acc, v, acc + IF(v="", 0, 1))) gives you that. Each row shows how many non-empty entries appeared up to and including that point. Plot it on a chart and you've got a velocity curve for data entry — useful when you're managing a team filling in a shared workbook.
Pre-365 users can fake the same effect with =COUNTA($A$2:A2) dragged down. Anchor the top of the range, leave the bottom relative, and each row sees one more cell than the previous. Same running total, no LAMBDA required.
Sometimes you have 80 columns and want to know how many actually have data in the header row. =COUNTA(1:1) handles that — it scans row 1 across all 16,384 columns of the worksheet and returns the count of non-empty header cells. Fast, no helper cells, no manual width adjustments. Use it after a paste from an external system to confirm your data didn't lose any columns in transit.
Pair this with a quick column-by-column completeness scan: drop =COUNTA(A:A) into a separate summary tab for each column in your data. Sort the summary by count descending and you see at a glance which columns are nearly full versus mostly empty. That's the single most useful data-quality dashboard you can build, and it takes about 90 seconds.
You've got a master list of expected items in column A and an actual list in column B. Quick gap check: =COUNTA(A:A)-COUNTA(B:B). If the answer is 12, you're missing 12 entries somewhere in B. To find which ones are missing, follow up with excel countif using each A value as the criterion against B. Anything returning 0 is missing from B. Built two ways, both fast.
=COUNTA(value1, [value2], ...) and it accepts up to 255 arguments. Unlike COUNT, which only tallies numeric values, COUNTA includes text, dates, errors, booleans, and even cells holding formulas that return an empty string. Use it any time the question is "how many of these cells were filled in" rather than "how many contain numbers.""" (empty string). The cell looks blank on screen but holds a zero-length string, so COUNTA treats it as non-empty. To fix, swap COUNTA for =COUNTIF(range,"?*")+COUNT(range), which uses a wildcard to skip empty strings while still catching real text and numbers. ISBLANK returns FALSE on these cells, which confirms the cause.=SUBTOTAL(3, A2:A100) — the 3 is COUNTA's function code inside SUBTOTAL. If you also want to ignore manually hidden rows, use 103: =SUBTOTAL(103, A2:A100). SUBTOTAL is the right tool whenever filter visibility matters.=COUNTA(UNIQUE(A2:A100)). On older Excel, use the classic array formula =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)). Both return the count of distinct values while ignoring blanks.=COUNTA(A:A) works without performance penalties on modern Excel. The function processes millions of cells in milliseconds, so range size is rarely the constraint.=COUNTIF(A2:A100,"?*"). The ?* pattern matches any text of one or more characters, ignoring numbers and empty strings. For more flexibility, see the excel count cells with text guide covering SUMPRODUCT and ISTEXT alternatives.