COUNTA counts the number of cells in a range that are not empty, including text, numbers, dates, errors, and logical values. It is the go-to function when you need to know how many entries exist, regardless of their type.
=COUNTA(value1, [value2], …)What does COUNTA do?
COUNTA is a statistical function that counts how many cells in a range contain anything at all. Unlike COUNT, which only counts numbers, COUNTA includes text, dates, numbers, logical values (TRUE/FALSE), and even error values. It simply asks: is this cell empty or not? If it has content, it is counted.
This matters because most real spreadsheets mix data types. A list of attendees might have names (text), arrival times (dates), and payment amounts (numbers). COUNTA gives you a single, reliable count of how many entries you have without worrying about the data type. It is perfect for checking completeness, counting responses, or summarising lists.
Imagine you manage a customer feedback log. You want to know how many customers responded, but some left only comments, others only a rating, and a few provided both. COUNTA on the response columns tells you exactly how many people engaged, helping you report participation accurately.
When to use COUNTA
- Counting how many employees have submitted timesheets
- Checking how many survey questions were answered
- Tallying the number of products listed in an inventory
- Counting non-empty cells in a column to verify data entry completeness
- Determining how many students submitted assignments
- Counting the number of unique entries in a list (with helper columns)
COUNTA syntax
=COUNTA(value1, [value2], …)| Argument | Required? | What to enter |
|---|---|---|
value1 | Required | The first item, cell reference, or range to count. It can be a number, text, date, logical value, error, or a range of cells. |
value2 | Optional | Additional items, cell references, or ranges to count. You can supply up to 255 arguments. |
What it returns: COUNTA returns a number representing the count of non-empty cells among the provided arguments.
COUNTA examples
1 Count non-empty cells in a single column
The goal is to find out how many cells in column A contain any data. The formula =COUNTA(A2:A6) examines each cell from A2 to A6. It counts cells that are not empty, regardless of whether they hold text or numbers. In this case, all five cells have content, so it returns 5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | |||
| 2 | Apple | 5 | ||
| 3 | Banana | |||
| 4 | Cherry | |||
| 5 | Date | |||
| 6 | Elderberry | |||
| 7 |
2 Count across multiple ranges
Here we want to count how many cells in two separate columns have entries. The formula =COUNTA(A2:A4, B2:B4) checks both ranges. It counts each non-empty cell in A2:A4 and B2:B4. Since all six cells contain data, the result is 6.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First Name | Last Name | |||
| 2 | John | Smith | 6 | ||
| 3 | Jane | Doe | |||
| 4 | Bob | Brown | |||
| 5 |
3 Count non-empty cells with mixed data types
This example shows COUNTA counting cells that contain text, numbers, and dates. The formula =COUNTA(A2:A6) looks at each cell in the range. It counts all non-empty cells, so text, numbers, and dates are all included. The result is 5 because every cell has some content.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data | |||
| 2 | Text | 5 | ||
| 3 | 100 | |||
| 4 | 2024-01-01 | |||
| 5 | TRUE | |||
| 6 | More text | |||
| 7 |
4 Count responses in a feedback log
A feedback log has a Comments column and a Rating column. Some customers left only comments, some only ratings, and some both. We want the total number of responses (any entry in either column). The formula =COUNTA(B2:B5, C2:C5) counts all non-empty cells in both columns. There are 3 comments and 3 ratings, but one customer (row 4) left both, so the total non-empty cells are 6? Wait, let's list: B2 has comment, C2 has rating; B3 has comment, C3 empty; B4 empty, C4 rating; B5 has comment, C5 rating. That's B2, B3, B5 (3 comments) and C2, C4, C5 (3 ratings) = 6 non-empty cells. But the goal is to count responses, not cells. Actually, COUNTA counts cells, so it returns 6. However, if we want the number of customers who responded, we need a different approach. But for this example, the formula counts non-empty cells, so expected is 6.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Comments | Rating | |||
| 2 | Alice | Great service | 5 | 6 | ||
| 3 | Bob | Slow delivery | ||||
| 4 | Carol | 4 | ||||
| 5 | Dave | Excellent | 5 | |||
| 6 |
5 Count non-empty cells in a sales report
A sales report lists regions and sales amounts. We want to know how many sales entries exist (non-empty sales cells). The formula =COUNTA(B2:B6) counts non-empty cells in the Sales column. All five cells have numbers, so it returns 5. This helps verify that every region has a sales figure recorded.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 5 | ||
| 3 | South | 1500 | |||
| 4 | East | 800 | |||
| 5 | West | 1700 | |||
| 6 | Central | 1100 | |||
| 7 |
Common COUNTA errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The formula includes a cell reference that is invalid or a text string that cannot be interpreted. | Check that all arguments are valid cell references or ranges. Avoid typing text directly unless it is enclosed in quotation marks. |
| #NAME? | The function name is misspelled, such as COUNTA written as COUNT A or COUNTA. | Ensure the function is spelled exactly as COUNTA and that it is available in your Excel version. |
| Incorrect count (too high) | Cells that look empty actually contain spaces, apostrophes, or other invisible characters. | Use TRIM or CLEAN to remove extra spaces, or filter for blanks to identify hidden characters. |
| Incorrect count (too low) | You accidentally included empty cells that contain formulas returning empty strings. | Remember that COUNTA counts cells with formulas that return empty text as non-empty. Use COUNTBLANK or adjust the formula to ignore such cells. |
Tips for using COUNTA
- Use COUNTA to quickly check if a column is fully populated before running other calculations.
- Combine COUNTA with COUNTCONT to count specific entries, or use it alone for total non-empty cells.
- When counting responses, be aware that COUNTA counts cells, not unique respondents; use helper columns or SUMPRODUCT for unique counts.
- To count only numbers, use COUNT; to count only text, use COUNTA minus COUNT.
- Press Ctrl+Shift+Enter for array formulas if you need to count non-empty cells across multiple ranges conditionally.
Frequently asked questions
What is the difference between COUNTA and COUNT?
COUNT only counts cells containing numbers, while COUNTA counts all non-empty cells, including text, dates, logical values, and errors.
Does COUNTA count cells with formulas that return an empty string?
Yes, COUNTA counts a cell as non-empty if it contains a formula, even if the formula returns an empty string ("").
Can COUNTA count cells across multiple sheets?
Yes, you can use COUNTA with 3D references like =COUNTA(Sheet1:Sheet3!A1:A10) to count non-empty cells across the same range on multiple sheets.
How do I count non-empty cells in a filtered list?
COUNTA counts all cells in the range, including hidden ones. To count only visible cells, use SUBTOTAL with function number 103 or 3.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. COUNTA reference