How to Use COUNT/COUNTA in Google Sheets
=COUNT(value1, [value2, ...]) and =COUNTA(value1, [value2, ...]).
What COUNT and COUNTA do
Both functions count cells, but they count different things. COUNT counts numeric values and ignores text. COUNTA counts all values, including text. Choosing the wrong one is the most common reason a count looks off.
Syntax
=COUNT(value1, [value2, ...])
=COUNTA(value1, [value2, ...])
value1: the first value or range to count.value2, ...(optional): more values or ranges.
According to Google's COUNT page, COUNT counts only numeric values, ignores text, and counts every numeric occurrence, duplicates included. The COUNTA page says COUNTA counts all values, including repeated ones, text, zero-length strings and whitespace. To count only distinct values, use COUNTUNIQUE.
A step-by-step example
Here is a small stock list in cells A1:B6. The quantity for Book is empty, and the one for Mug is the text n/a.
| A | B | |
|---|---|---|
| 1 | Item | Qty |
| 2 | Pen | 3 |
| 3 | Book | |
| 4 | Cap | 5 |
| 5 | Mug | n/a |
| 6 | Tape | 2 |
To count the cells that hold a number:
=COUNT(B2:B6)
This returns 3: the values 3, 5 and 2. The empty cell and the text n/a are skipped.
To count every cell that holds a value:
=COUNTA(B2:B6)
This returns 4: the numbers 3, 5 and 2, plus the text n/a. The empty cell is not counted.
Which one should you use?
- Use COUNT to see how many numbers have been entered.
- Use COUNTA to see how many cells are filled in, whatever they hold.
- Use COUNTIF or COUNTIFS when you need to count only values that meet a condition.
Common mistakes
- Numbers stored as text. COUNT skips them, but COUNTA counts them.
- Cells that look empty but are not. A cell with a space or an empty-string formula result is counted by COUNTA.
- Expecting a count of unique values. Both functions count duplicates. Use COUNTUNIQUE for distinct values.
Try it yourself
Use the stock list above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Count the numbers
Write a formula that counts how many numbers are in B2:B6.
Exercise 2: Count the filled cells
Write a formula that counts how many cells in B2:B6 hold any value, text included.
What to learn next
- COUNTIF: count the rows that match a condition.
- COUNTIFS: count rows that meet several conditions.
- UNIQUE: remove duplicates from a list.
- Count cells with text: count text cells and partial matches.
- MIN and MAX: find the smallest and largest values.
- COUNTBLANK: count empty cells.
Syntax and rules in this guide were checked against Google's COUNT/COUNTA help page. See all guides.