What it does
For COUNTA it does not matter what a cell contains; if it is not empty, it counts. =COUNTA(A2:A500) returns the number of rows whose name column is filled, which is the number of records in the list.
Watch out for two traps. A cell holding only a space looks empty but is not, so it counts. A formula that returns empty text, such as ="", also makes the cell non-empty. If the result is larger than you expect, look for these two cases first.
To count only numbers use COUNT; to count empty cells, COUNTBLANK; to count a particular value, COUNTIF. In Microsoft 365, the number of distinct values in a column is =COUNTA(UNIQUE(B2:B500)).
Syntax
=COUNTA(value1, [value2], ...)=BAĞ_DEĞ_DOLU_SAY(değer1; [değer2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
value1 | Required | The first range, cell or value whose non-empty cells are counted. |
value2 | Optional | Optional further ranges or values, up to 255 arguments. |
Examples
1Number of records and filled-in fields
F2 counts the records with a company name and G2 those with an email address. The columns hold text, so COUNT would return 0 here.
=COUNTA(A2:A6)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Company | Contact | Records | With Email | |||
| 2 | Brown & Sons | Emma Brown | sales@brownandsons.co.uk | 5 | 3 | ||
| 3 | Northgate Ltd | Lucas Gray | |||||
| 4 | Harbour Trade | info@harbourtrade.co.uk | |||||
| 5 | Star Market | Ava Thompson | |||||
| 6 | Oak Builders | Noah Clark | support@oakbuilders.com |
- F2
=COUNTA(A2:A6) - G2
=COUNTA(C2:C6)
The list has 5 companies; 3 of them have an email address.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2How many questions did each survey answer?
Answers can be text or numbers. The formula in F2 is filled down and counts how many of the four questions were answered on each row.
=COUNTA(B2:E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Survey | Satisfaction | Score | Recommend | Comment | Answered |
| 2 | A-01 | Satisfied | 9 | Yes | Fast delivery | 4 |
| 3 | A-02 | Neutral | 6 | 2 | ||
| 4 | A-03 | 8 | Yes | 2 | ||
| 5 | A-04 | Unsatisfied | 3 | No | Parcel arrived damaged | 4 |
- F2 (filled down to F5)
=COUNTA(B2:E2)
The first and last surveys answered all 4 questions; A-02 answered only 2.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3How many different customers placed orders?
The same customer placed several orders. UNIQUE in Microsoft 365 lists each customer once, and COUNTA counts that list.
=COUNTA(B2:B7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Customer | Orders | Customers | |
| 2 | S-1001 | Brown & Sons | 6 | 3 | |
| 3 | S-1002 | Northgate Ltd | |||
| 4 | S-1003 | Brown & Sons | |||
| 5 | S-1004 | Harbour Trade | |||
| 6 | S-1005 | Northgate Ltd | |||
| 7 | S-1006 | Brown & Sons |
- D2
=COUNTA(B2:B7) - E2
=COUNTA(UNIQUE(B2:B7))
6 orders came from 3 different customers.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Count too high
Cause: Some cells that look empty contain a space or a formula returning empty text; they count as non-empty.
Fix: To count only cells that really hold something, use
=SUMPRODUCT(--(TRIM(A2:A500)<>"")).- One too many
Cause: The range includes the header row (
A1:A500), and the header is a non-empty cell too.Fix: Start the range at the first data row:
A2:A500.
Good to know
- To see how many fields of a form are filled in on each row, use
=COUNTA(B2:F2)and fill it down. - To find out whether numbers are stored as text, compare COUNTA with COUNT on the same range.
In Nexell
Get Nexell to do it from a sentence
Add a summary showing how many customers are in the list and how many have an email address
Convert to tableBring your customer list into the tool and type this. Nexell builds the counts with real Excel formulas and shows them in the preview first; in the downloaded file, the counts update as you add information.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I count non-empty cells in Excel?
=COUNTA(A2:A500) counts the cells that are not empty. To count only numbers, use COUNT.
Why does COUNTA count cells that look empty?
Those cells contain a space or a formula that returns empty text, and Excel treats them as non-empty. Select the cell and look at the formula bar, or check it with =LEN(A2).
Related functions
- COUNTBAĞ_DEĞ_SAY · Counts cells that contain numbers.
- COUNTBLANKBOŞLUKSAY · Counts empty cells.
- COUNTIFEĞERSAY · Counts cells that meet a condition.
- ROWSSATIRSAY · Number of rows in a range.
- UNIQUEBENZERSİZ · List of distinct values (count them with COUNTA). TRUE as the third argument keeps values that occur once.