COUNTA Function in Excel, Google Sheets, Airtable & Notion
COUNTA counts every cell that is not empty, whatever it contains: numbers, text, dates, TRUE/FALSE and even error values.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | COUNTA(value1, [value2], ...) |
| Google Sheets | ✔ Built in | COUNTA(value1, [value2, ...]) |
| Airtable | ✔ Built in | COUNTA(value1, [value2, ...])Counts non-empty values. In a rollup, COUNTA(values) counts linked records where the field is filled in. |
| Notion | ≈ Workaround | list.filter(!empty(current)).length()No COUNTA function; filter out empty values and take the length. A rollup set to “Count values” does the same. |
| Excel | =COUNTA(A2:A6) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =COUNTA(A2:A6) | open sheets.new ↗ |
| Airtable | COUNTA("Ann", "", "Ben", 42, "Cara") | |
| Notion workaround | ["Ann", "", "Ben", 42, "Cara"].filter(!empty(current)).length() |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Name Ann Ben 42 Cara
Arguments
| Argument | What it means |
|---|---|
value1 | The first value, cell or range to count. |
[value2], ... optional | More values or ranges. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=COUNTA(A2:A6) | 4 | Tester data: the blank A3 is not counted; text and the number 42 are. | |
=COUNTA("a", 1, TRUE) | 3 | Any kind of value counts. | |
=COUNTA(A:A) - 1 | Rows of data | Subtract 1 for the header row. |
Examples use Excel / Google Sheets syntax unless noted.
How COUNTA works
COUNTA is the usual way to count how many rows have been filled in: responses received, names in a list, tasks with an owner. Unlike COUNT, it does not care what kind of value is in the cell, only that the cell is not empty.
The trap is cells that look empty but are not. A formula that returns an empty string, such as =IF(B2>0, B2, ""), makes the cell look blank, yet COUNTA counts it. So does a cell containing a single space. If your count is too high, count only real text with =COUNTIF(A2:A100, "?*"), or exclude empty strings with =SUMPRODUCT(--(LEN(A2:A100)>0)).
Airtable has COUNTA too; in a rollup field, COUNTA(values) counts linked records whose field is filled in, while COUNTALL(values) counts every linked record. Notion has no COUNTA, but list.filter(!empty(current)).length() or a rollup set to “Count values” gives the same answer.
Frequently Asked Questions
- Why does COUNTA count cells that look empty?
- They contain something: a formula returning "", a space, or an apostrophe. Use
COUNTIF(range, "?*")to count only cells with visible text. - How do I count empty cells?
- Use
COUNTBLANK(range), which also counts cells containing "" from a formula.