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 Sheets 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 tally all cells with numbers within a specific data range. In other words, COUNT deals with numeric values or those that are stored as numbers in Google Sheets.

The syntax of Google Sheets COUNT and its arguments is 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 you use 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).

If you change the content of any cell from within the counted range, the function will automatically recalculate the result.

If multiple cells contain the same value, COUNT in Google Sheets will return the number of all its instances in those cells.

So 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.

Tip. To count unique values in the range, use the COUNTUNIQUE function instead.

Google Sheets COUNTA works in a similar way and its syntax is alike:

COUNTA(value1, [value2,…])
  • Value (required) — the values you need to count.
  • Value2, value3, etc. (optional) — any other additional values to count.

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

COUNTA will 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 cells 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 stores as text. Both functions ignore completely empty cells.

Comparing COUNT and COUNTA.

Since date and time are stored and counted as numbers in Google Sheets, both COUNT and COUNTA process A4 and A5.

A10 is completely empty — thus, ignored by both functions.

Other cells were counted by Google Sheets COUNTA:

=COUNTA(A2:A12)

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

A8 on its turn has a number stored as text which wasn't processed by Google Sheets COUNT.

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 Google Sheets COUNT and COUNTA — examples included

Let's take a closer look at how the COUNT function is used in a Google spreadsheet and how you can benefit from using it in Sheets.

Suppose you have a list of students' grades. Here are the ways COUNT can help: Various COUNT formulas for students' grades.

Look, there are 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 a 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:

=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 Google Sheets COUNTA work with the same data? COUNTA formulas 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 ones.

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

The customers who haven't bought anything are not considered for 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 counted range.

For example, I merge B6:C6 and B9:C9 and count column B. My formula below will count 65, 55, 70, 55, 81, 88, 61, 92:

=COUNT(B2:B)

COUNT, COUNTA and merged cells.

But with a slightly different range, the formula returns another result. It 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. =COUNTA(C2:C) 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.

Count uniques in Google Sheets

If you'd rather count only unique values in the range, you'd better use the COUNTUNIQUE function. It requires literally one argument that can be repeated: a range or a value to process.

=COUNTUNIQUE(value1, [value2, ...])

The formulas in spreadsheets will look as plain as this:

=COUNTUNIQUE(A2:A10)

Count unique records in Google Sheets.

You can also enter multiple ranges and even records themselves directly to the formula:

=COUNTUNIQUE(A2:A5,A7:A10,"Davies","Smith")

Scan multiple ranges/values for uniques.

Count with multiple criteria — COUNTIF in Google Sheets

In case the standard count is not enough and you need to count only specific values based on some conditions, there is another special function for that — COUNTIF. All its arguments, usage, and examples are covered in another special blog post.

To count & highlight duplicates in Google Sheets, visit this article instead.

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

Table of contents

51 comments

  1. Hi,

    I need to work out the number of guests attending our site per day I have a table that shows (in separate columns) adults, children, arrival, departure. I need to be able to look at all the dates - say September - and work out how many guests have visited each day, ideally, also counting how many bookings we have each day.

    • Hi Greg,

      For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.

      I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment. I'll look into it and try to help.

  2. Hello,

    I am trying to create a tally of how many gift cards that I have given away through work. I have a drop down in one column with the gift card brands on there (McDonalds, Subway etc.)

    Example: I have given away 3 Subway cards and 1 McDonalds card, now when I select Subway from the dropdown, I want that to reflect as 1 given away in Subways row and the "given away" column. Same goes for McDonalds, Tim Hortons and all the rest.

    Thank you

  3. Hi,

    I'm trying to use COUNTA to total up the number of appointment with a given client per month. I'm having trouble when I have more than 1 date per cell, as in the example below. In week 2, I met with the client two times (on December 11 and December 13). It would be ideal if these dates could each be counted, so that the result would be 4 total monthly sessions, not 3. But COUNTA counts them as 1 item. Is there anyway for them to each be counted? Thanks so much!

    Client. Week1. Week 2. Week 3. Week 4 # Sessions
    X. 12/4 12/11, 12/13. 12/18. 12/25. 3

  4. Hi there,

    I have a sheet where each column represents a garment size. Each garment is a different price. I need a tally of populated cells but each cell populated is worth a certain value. I wish to calculate the total price based purely on the tally of populated cells.

    • Hi Jan,

      I'm sorry, your task is a bit confusing. Could you please describe it in more detail, maybe with some examples of what's in cells and what the result should be?

  5. How do I calculate the number of times a word appears in a column. For example if I have a rooming list with 150 people, i want to know how many are in single rooms and how many are in twin rooms. This is stated in a particular column I just want to be able to calculate automatically

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)