COUNTA Function in Excel and Google Sheets
Purpose
Count all non-blank cells in a range, regardless of whether they contain text, numbers, dates, or other values
Return value
An integer representing how many cells in the supplied arguments contain any value (including text, numbers, dates, logical values, and errors)
COUNTA is the counting function I reach for whenever I want to know how many cells have anything in them at all -- not just numbers, but text, dates, logical values, anything. How many rows in this submission form have been filled in? How many tasks have been assigned to someone? How many records in this import have a value in the required field? All of these are COUNTA questions. The distinction from COUNT is the one that matters most: COUNT counts only numbers, COUNTA counts everything that is not a genuine blank. Together with COUNTBLANK they give you a complete picture of any column -- COUNTA tells you what is there, COUNTBLANK tells you what is missing, and the two should always add up to the total rows in the range. That invariant is one of the fastest data-quality checks I run on any new dataset.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=COUNTA(value1, [value2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| value1 | Required | The first range, cell, or value to count. Any non-blank cell is counted regardless of data type. |
| value2 ... | Optional | Additional ranges, cells, or values, up to 255 arguments. Counts from all arguments are summed together into one total. |
How it works
COUNTA counts every cell that is not completely empty. A cell is considered non-blank if it contains text (including an empty string returned by a formula), a number, a date, a logical value (TRUE or FALSE), or an error value. Only a genuinely empty cell -- one that has never had content or was cleared with Delete -- is excluded. This means a cell containing =IF(A1>0,"Yes","") that evaluates to "" is still counted by COUNTA because it contains a formula. Use COUNTBLANK for the inverse count. Use COUNT for numeric-only counting. Use COUNTIF with the "*" wildcard -- =COUNTIF(range,"*") -- for a text-only count that excludes numbers. Divide COUNTA by ROWS(range) for a completion percentage. For non-blank counting that excludes formula-returned empty strings, use =SUMPRODUCT((LEN(TRIM(range))>0)*1) as a more precise alternative.
Examples
1
Count how many tasks in a task list have been assigned to someone, treating any entry in the Assignee column as filled in.
| A | B | |
|---|---|---|
| 1 | Task | Assignee |
| 2 | Design mockup | Alice |
| 3 | Write copy | |
| 4 | Review budget | Bob |
| 5 | Send invoice | Alice |
| 6 | Update CRM | |
| 7 | Book venue | Carol |
| 8 | Draft report | |
| 9 | Schedule call | Bob |
| 10 | Prepare slides | Alice |
| 11 | Send summary | |
| 12 | Count assigned | =COUNTA(B2:B11) |
Row 2: 6
— Six tasks have an assignee filled in: rows for Design mockup, Review budget, Send invoice, Book venue, Schedule call, and Prepare slides. The four blank rows (Write copy, Update CRM, Draft report, Send summary) are excluded.
COUNTA counts any non-blank entry -- it does not care whether the content is Alice, Bob, Carol, or any other value. If you need to count only a specific assignee, use COUNTIF(B2:B11,"Alice") instead.
2
Compare COUNT and COUNTA on the same column to see how many cells have numeric values versus how many have any entry at all.
| A | B | |
|---|---|---|
| 1 | Entry | Value |
| 2 | 1 | 82 |
| 3 | 2 | Absent |
| 4 | 3 | 91 |
| 5 | 4 | |
| 6 | 5 | 78 |
| 7 | 6 | TRUE |
| 8 | 7 | 85 |
| 9 | 8 | |
| 10 | 9 | 94 |
| 11 | 10 | Pending |
| 12 | COUNT (numbers) | =COUNT(B2:B11) |
| 13 | COUNTA (any entry) | =COUNTA(B2:B11) |
Row 2: 8
— COUNTA returns 8 -- it counts the 5 numeric scores plus Absent, TRUE, and Pending. COUNT returns 5 -- only the numeric scores. The two blank cells are excluded by both functions.
The gap between COUNTA (8) and COUNT (5) is 3 -- those three cells have non-numeric content that will be excluded from SUM, AVERAGE, and any other numeric function. This COUNT vs COUNTA comparison is one of the fastest data-quality checks I run on any imported dataset.
3
Calculate the completion rate of a required field by dividing COUNTA by the total number of rows.
| A | B | |
|---|---|---|
| 1 | Respondent | Answer |
| 2 | 1 | Yes |
| 3 | 2 | No |
| 4 | 3 | |
| 5 | 4 | Yes |
| 6 | 5 | |
| 7 | 6 | No |
| 8 | 7 | Yes |
| 9 | 8 | |
| 10 | 9 | Yes |
| 11 | 10 | No |
| 12 | Completion rate | =COUNTA(B2:B11)/ROWS(B2:B11) |
Row 2: 70%
— 7 of 10 respondents answered the question. COUNTA returns 7, ROWS returns 10, 7/10 = 0.70 which formats as 70% when the cell is formatted as Percentage.
Format the result cell as Percentage to display 70% instead of 0.7. This completion rate pattern -- COUNTA divided by ROWS -- is the standard formula I use at the top of every survey and data-entry tracking sheet.
Common use cases
1. Count how many rows in a required field have been filled in for a data-completeness check
=COUNTA(B2:B100)
2. Calculate a form completion rate by dividing COUNTA by ROWS
=COUNTA(B2:B100)/ROWS(B2:B100)
3. Compare COUNTA and COUNT to identify non-numeric entries in a column that should be all numbers
=COUNTA(B2:B100)-COUNT(B2:B100)
4. Count non-blank cells across multiple separate columns in a single formula
=COUNTA(A2:A100,C2:C100,E2:E100)
5. Use as the denominator in a response rate calculation for a survey tracking sheet
=COUNTIF(B2:B100,"Yes")/COUNTA(B2:B100)
Common errors
COUNTA counts cells you expected to be blank
The cells contain formulas that return empty strings, or they contain space characters. Both look blank visually but are non-blank to COUNTA.
Fix: Use COUNTBLANK to check the opposite -- if COUNTA + COUNTBLANK does not equal ROWS(range), some cells have formula-returned empty strings. To count only visibly filled cells use =SUMPRODUCT((LEN(TRIM(B2:B100))>0)*1).
Completion rate shows as a decimal not a percentage
The result cell is formatted as General or Number rather than Percentage. COUNTA/ROWS gives a decimal like 0.70 that needs percentage formatting to display as 70%.
Fix: Select the result cell and apply Percentage format (Ctrl+Shift+% in Excel).
COUNTA count is lower than expected
Some cells that appear to have content are genuinely empty -- they may look filled due to adjacent cell overflow or cell border styling.
Fix: Click the apparently-filled cell and look at the formula bar. If the formula bar shows nothing, the cell is genuinely blank and correctly excluded by COUNTA.
Tips and variations
Use COUNTA + COUNTBLANK = ROWS as a data integrity check
COUNTA(range) + COUNTBLANK(range) must always equal ROWS(range) * COLUMNS(range). If the sum is off, some cells contain formula-returned empty strings that COUNTA counts but look blank. This invariant is the first check I run when a completion percentage does not add up.
=COUNTA(B2:B100)+COUNTBLANK(B2:B100)
Divide by ROWS for a completion percentage
=COUNTA(B2:B100)/ROWS(B2:B100) formatted as Percentage gives a live completion rate that updates as data is entered. I put this at the top of every data-entry column in shared workbooks so everyone can see progress at a glance.
=COUNTA(B2:B100)/ROWS(B2:B100)
Subtract COUNT from COUNTA to find non-numeric entries
=COUNTA(B2:B100)-COUNT(B2:B100) counts the cells that have something in them but not a number -- text, TRUE/FALSE, errors. When this number is non-zero in a column that should be purely numeric, those cells need investigation.
=COUNTA(B2:B100)-COUNT(B2:B100)
Excel vs Google Sheets
Excel vs Google Sheets
COUNTA works identically in Excel 2003 and newer and every version of Google Sheets. The non-blank counting behaviour, treatment of formula-returned empty strings, and multiple-argument syntax are the same in both applications.
Related reading
Frequently asked questions
COUNTA counts every cell in a range that is not completely empty -- text, numbers, dates, logical values (TRUE/FALSE), error values, and formulas that return an empty string all count as non-blank. Only cells that are genuinely empty (never touched or cleared with the Delete key) are excluded. COUNTA is the complement of COUNTBLANK.
COUNT counts only cells that contain numbers, including dates. COUNTA counts every non-blank cell regardless of data type -- text, numbers, dates, logical values, and errors all qualify. If your column mixes text labels and numbers, COUNT gives a lower result than COUNTA. Use COUNT when you specifically want numeric entries. Use COUNTA when any entry at all should be counted.
Yes. A cell containing a formula like =IF(A1>0,"Yes","") that evaluates to an empty string is not truly blank -- it contains a formula -- so COUNTA counts it. This is the key difference from COUNTBLANK: both COUNTA and COUNTBLANK treat formula-returned empty strings the same way. If you need to count only cells with visible non-empty content, use SUMPRODUCT with LEN: =SUMPRODUCT((LEN(TRIM(A2:A100))>0)*1).
Count the non-blank cells in a required field column: =COUNTA(B2:B100) gives the number of rows where that field has been filled in. Divide by the total rows to get a completion rate: =COUNTA(B2:B100)/ROWS(B2:B100). Compare COUNTA across multiple required columns to find which fields have the lowest completion.
Yes. COUNTA accepts up to 255 arguments separated by commas, each of which can be a range, individual cell, or literal value: =COUNTA(A2:A100,C2:C100,E5) counts non-blank cells across three separate ranges in one formula. The counts from all arguments are combined into a single total.