【Google Sheets】COUNT vs. COUNTA｜count cells containing numbers vs. anything

Why is the name of these functions COUNT, COUNTA so confusing?

To differentiate, imagine COUNT literally; – it counts numbers. So, it only counts cells that contain numbers.

On one hand, imagine COUNTA as COUNT+α. It counts more than numbers, like cells containing text, logical values (=, >).

counts cells containing numbers…

Anyway… first you see their syntax separately.

1 Step # 1：the Syntax and Example of COUNT
2 Step # 2：the Syntax and Example of COUNTA
- 2.1 Why does it look like empty?

Step # 1：the Syntax and Example of COUNT

　COUNT

to count cells that contain numbers in a dataset

COUNT ( value1, [value2, …] )

　value1：The first value or range to be considered for counting.
　value2, …： Additional values or ranges to be considered for counting (optional).

※ The function counts only numeric values; text values are ignored.

Why is it followed by value1 though it counts cells in a range

It is not necessarily followed by a range. It can be just a single cell like “B1” or figure like “6”.

　Example 1

Count the number of cells that contain numeric values within each background color.

　Answer

1 =COUNT(B2)
4 =COUNT(B3:B6)
5 =COUNT(C2:C6,D2:D6), =COUNT(C2:D6) is also available
5 =COUNT(E2:F6)

　1, As the cell B1 contains one numerical value, answer is “1”.
　2, As the cell range B3:B6 contains 4 numerical values, answer is “4”.
　3, The cell range B3:B6 that contains “products name” is ignored as it’s text.
　4, Sales column that contains “yen” is ignored as it’s text. Date (and Time) values are, however, countable as these are stored as serial numbers and it’s just interpreted as a date in Sheets.

=COUNT(C2:C6,D2:D6), =COUNT(C2:D6) is also available

What does it mean?

Means you can regard each range as value1 and value2 independently, though you can also regard them as the single range like C2:D6.

So, it can be useful if those ranges are located separately from one another?

Exactly. Then, let’s see how COUNTA works.

Step # 2：the Syntax and Example of COUNTA

　COUNTA

to count cells that contain values in a dataset

COUNTA ( value1, [value2, …] )

　value1：The first value or range to be considered for counting.
　value2, …：Additional values or ranges to consider when counting(optional).

※ COUNTA also counts text.
※ You can think of COUNTA as counting all cells that aren’t empty.

So, you can think of COUNTA as COUNT+α or COUNT+Anything because it counts anything including numbers, text, logical values, and error values.

　Example 2

Count the number of cells that contain values within the thick blue border.

　Answer

　1,　Select the cell D3 and type =COUNTA()
　2,　Define the cell range D2:D7 as “range 1”.
　3,　There’re 5 cells that contain values except for the blank-cell B4.

Though it’s written that “ ※ You can think of COUNTA as counting all cells that aren’t empty.” above,

you can really think it counting all non-blank cells.

Yes you can. But there’s case where a black cell actually has a value while it looks like empty.

Why does it look like empty?

If you count the number of cells that contain values in the same cell range B2:B7..

D3 =COUNTA(B2:B7)

That’s true! It returns “6” though it seems the exact same question we’ve done earlier.

All the cells that are appearing as blanks are not really blank cells sometimes. In Sheets, real blank cells are the cells that do not contain any content including the empty string =”_” that makes cells look empty. It’s mainly used in the formula to see if the cell is blank.