Excel COUNTA Function — Complete Guide (2026)
Excel COUNTA counts non-empty cells. Learn the syntax, COUNT vs COUNTA vs COUNTBLANK, the empty-string trap, and real-world examples that actually work.
Excel COUNTA Function — Complete Guide (2026)
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.
Why COUNTA Exists at All
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.
The One-Line Definition
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.
Who Should Bookmark This Function
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.
COUNTA at a Glance
COUNTA counts ANY non-empty cell — including hidden traps
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.
Syntax and Arguments
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.
COUNTA vs COUNT vs COUNTBLANK
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.
Real-World COUNTA Examples
Forget the textbook. Here's how the counta function in excel shows up in actual sheets people maintain at work.
Attendance Tracker
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.
Survey Completion Rate
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.
Project Task Tracker
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.
Inventory Audit
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.
Counting Open Form Fields
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.
Multi-Sheet Roll-Up
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.
Quick Header Check
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.
Data Validation Counter
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.
Why COUNTA Sometimes Lies
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.
The Cleaner COUNTA Replacement
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.
When COUNTA Is Actually Right
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."
The COUNTBLANK Sanity Check
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.
Counting Visible Cells After a Filter
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.
Errors Still Count — Watch Out
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.
Merged Cells Confuse the Count
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.
Counting Filtered, Hidden, and Unique Cells
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 Best Practices Checklist
- ✓Use COUNTA for any "how many cells were filled in" question — it's the simplest tool for the job
- ✓Switch to COUNT if you only want numeric values counted, not text labels
- ✓Use COUNTBLANK to find gaps — and remember it counts empty strings as blank, the opposite of COUNTA
- ✓When formulas return "" in your range, replace COUNTA with COUNTIF(range,"?*")+COUNT(range) for accuracy
- ✓For filtered ranges, use SUBTOTAL(3, range) instead of COUNTA to count only visible cells
- ✓Use SUBTOTAL(103, range) when you also want to ignore manually hidden rows
- ✓Combine COUNTA(UNIQUE(range)) in Excel 365 to count distinct non-empty values
- ✓Use COUNTIFS for multi-criteria counting instead of stacking COUNTA inside IF formulas
- ✓Verify with COUNTA + COUNTBLANK = total cells — if they don't sum to the range size, investigate
- ✓Always double-check by clicking suspicious blank-looking cells — empty strings hide there
Beyond Basic Counting: COUNTA Power Patterns
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.
Dynamic Range References
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.
Last-Used Row Detection
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.
Progress Percentage
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.
Counting Rows Across Multiple Sheets
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.
COUNTA as a Sanity Check Inside Other Formulas
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.
The Half-Empty Detector
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.
COUNTA Inside an Array Formula
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.
Counting Visible Columns in a Wide Table
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.
Comparing Two Lists For Completeness
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 — When to Use It, When to Skip It
- +Dead simple — single argument, zero learning curve
- +Counts everything: text, numbers, dates, errors, booleans
- +Works with whole-column references like A:A without performance issues
- +Accepts up to 255 arguments — combine ranges from anywhere
- +Available in every Excel version since 1993
- +Pairs cleanly with COUNTBLANK to partition any range
- −Counts empty strings ("") as non-empty — the #1 cause of wrong counts
- −Ignores filters — counts hidden rows too (use SUBTOTAL(3,...) instead)
- −No criteria support — for conditional counting use COUNTIF or COUNTIFS
- −Counts duplicates as separate values — wrap in UNIQUE for distinct counts
- −Errors still count — a column full of #N/A returns the column length
- −No way to exclude specific value types without nesting other functions
Excel Questions and Answers
About the Author
Business Consultant & Professional Certification Advisor
Wharton School, University of PennsylvaniaKatherine Lee earned her MBA from the Wharton School at the University of Pennsylvania and holds CPA, PHR, and PMP certifications. With a background spanning corporate finance, human resources, and project management, she has coached professionals preparing for CPA, CMA, PHR/SPHR, PMP, and financial services licensing exams.