Nov
16

Google Sheets COUNT and COUNTA functions with formula examples

The COUNT function in Google Sheets is one of the easiest to learn and extremely helpful to work with.

Even though it looks simple, it's capable of returning interesting and useful results, especially in combination with other Google functions. Let's get right into it.

What is COUNT and COUNTA in a Google spreadsheet?

The COUNT function in Google Sheets allows you to count the number of certain values within a specific data range.

The syntax of the COUNT function and its arguments look as follows:

COUNT(value1, [value2,…])
  • Value1 (required) - stands for a value or a range to count within.
  • Value2, value3, etc. (optional) - additional values that are going to be covered as well.

What can be used as an argument? The value itself, cell reference, range of cells, named range.

What values can you count? Numbers, dates, formulas, logical expressions (TRUE/FALSE).

In other words, COUNT deals with numeric values or those that are stored as numbers in Google Sheets.

Besides, if you change the content of the cell that falls into the counting range, the formula will automatically recalculate the result.

If multiple cells contain the same value, COUNT will return the number of its appearances in those cells.

To be more precise, the function counts the number of times numeric values appear within the range rather than checks if any of the values are unique.

COUNTA works in a similar way. Its syntax is also analogous to COUNT:

COUNTA(value1, [value2,…])
  • Value (required) - the values we need to count.
  • Value2, value3, etc. (optional) - additional values to use in counting.

What's the difference between COUNT and COUNTA? In the values they process.

COUNTA can count:

  • Numbers
  • Dates
  • Formulas
  • Logical expressions
  • Errors, e.g. #DIV/0!
  • Textual data
  • Cells containing leading apostrophe (') even without any other data in them. This character is used at the beginning of the cell so that Google treats the string that follows as text.
  • Cells that look empty but in fact contain an empty string (=" ")

As you can see, the main difference between the functions lies in the ability of COUNTA to process those values that Google Sheets service stores as text. Both functions ignore completely empty cells.

Have a look at the example below to see how the results of using COUNT and COUNTA differ depending on the values:
COUNT and COUNTA in comparison

Since dates and time are stored and counted as numbers in Google Sheets, A4 and A5 were counted by both, COUNT and COUNTA.

A10 is completely empty, thus it was ignored by both functions.

Other cells were counted by the formula with COUNTA:

=COUNTA(A2:A12)

Both formulas with COUNT return the same result because A8:A12 range doesn't include numeric values.

A8 cell has a number stored as text which wasn't counted by the COUNT function.

The error message in A12 is entered as text and considered by COUNTA only.

Tip. To set more precise calculation conditions, I recommend you to use COUNTIF function instead.

How to use COUNT and COUNTA in Google Sheets - examples included

Let's take a closer look at how the COUNT function is used in a Google spreadsheet and how it can benefit our work with tables.

Suppose we have a list of students' grades. Here are the ways COUNT can help:
Variations of COUNT for students' grades

As you can see, we have different formulas with COUNT in column C.

Since column A contains surnames, COUNT ignores that whole column. But what about cells B2, B6, B9, and B10? B2 has number formatted as text; B6 and B9 contain pure text; B10 is completely empty.

Another cell to bring your attention to is B7. It has the following formula in it:

=COUNT(B2:B)

Notice that the range starts from B2 and includes all other cells of this column. This is a very useful method when you often need to add new data to the column but want to avoid changing the range of the formula every time.

Now, how will COUNTA function work with the same data?

COUNTA for students' grades

As you can see and compare, the results differ. This function ignores only one cell - the completely empty B10. Thus, bear in mind that COUNTA includes textual values as well as numeric.

Here's another example of using COUNT to find an average sum spent on products:

COUNT to find the average

The customers who haven't bought anything were omitted from the results.

One more peculiar thing regarding COUNT in Google Sheets concerns merged cells. There is a rule that COUNT and COUNTA follow to avoid double counting.

Note. The functions take into account only the leftmost cell of the merged range.

When the range for counting contains merged cells, they will be treated by both functions only if the upper left cell falls within the range for counting.

For example, if we merge B6:C6 and B9:C9, the formula below will count 65, 55, 70, 55, 81, 88, 61, 92:

=COUNT(B2:B)

COUNT, COUNTA and merged cells

At the same time, the same formula with a slightly different range will work only with 80, 75, 69, 60, 50, 90:

=COUNT(C2:C)

The left parts of the merged cells are excluded from this range, therefore are not considered by COUNT.

COUNTA works in a similar way.

  1. =COUNTA(B2:B) counts the following: 65, 55, 70, 55, 81, 88, 61, "Failed", 92. Just like with COUNT, empty B10 is ignored.
  2. =COUNTА(С2:С) works with 80, 75, 69, 60, 50, 90. Empty C7 and C8, as in the case with COUNT, are ignored. C6 and C9 are omitted from the result since the range doesn't include the leftmost cells B6 and B9.

I really hope that this article will assist your work with Google Sheets, and that COUNT and COUNTA functions will serve you well.

You may also like:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard