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:
- 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:
- 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:
- 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.
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:
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:
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:
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?
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:
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.
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:
But with a slightly different range, the formula returns another result. It will work only with 80, 75, 69, 60, 50, 90:
The left parts of the merged cells are excluded from this range, therefore are not considered by COUNT.
COUNTA works in a similar way.
=COUNTA(B2:B)counts the following: 65, 55, 70, 55, 81, 88, 61, "Failed", 92. Just like with COUNT, empty B10 is ignored.
=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.
The formulas in spreadsheets will look as plain as this:
You can also enter multiple ranges and even records themselves directly to the formula:
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.
Every time I use a COUNT function, I am getting a date as a result. How do I fix this?
Select your result, go to Format > Number an format cells as you need, e.g. number.
I have a list of task to complete but each task is assigned to a specific person, which I have made by drop down, how do I calculate how many of their tasks are completed out of the number of tasks assigned to them?
Also how do I assign a deadline, to know if their task is completed on time? so its automatically updated if its on track, over due or completed?
I also need to have their results appear on a another sheet, please help this is too complicated for me
To count only completed tasks, you need to use the COUNTIF function.
To check if the task is on track, overdue or completed, use the IF function.
To learn how to refer to the data on another sheet, please read this explanation.
How would I go about finding the count for customers that ordered a sample order then purchased a normal order after said sample order. I have a column of customer IDs which are unique to each customer, and another column of order type; this displays if their order was a "Sample" or "Normal".
Please look through the following article, it may help you better: Google Spreadsheet COUNTIF function with formula examples
Is there a way to use COUNTA to count the number of cells that are not empty, but to count merged cells as if they are not merged? So if I have merged 3 cells and assigned a single value in the resultant merged cell, COUNTA would still count this as 3 cells in which text was found?
There's a way with a complex formula. For example, if E4:G4 are merged, the following formula will return 3:
Simple question, but haven’t seen it. I just need to show the count of my columns. ie: there’s 40 people coming to our event. It’s important to see the count of each person in a separate column. I know how to show how many cells are in a Collumn but not how to show each number (ie: 1,2,3,4,5,6,etc)?? Please help
I'm sorry, your task is not entirely clear to me. Please describe it in more detail, I'll try to help.
How do you count how much is the total of a particular category with a corresponding amount?
Column A - Category of Expense
Column B - Amount of Cost
If I understand your question correctly, you'd better use COUNTIF function.
May I know what formula/count can I use in counting the number of positions in each department?
For example in Google Spreadsheet,
A is the department and B is the Position (e.g It Staff, It Manager, accounting manager) . At A, I have IT, Admin and accounting . At C, I manually counted 4 position relating to B -Admin.
If you have any ideas what formula I can use for this problem rather than counting them one by one, I would appreciate it very much. Thanks!
At B, I manually counted 4 position relating to A -Admin for example.
You can use COUNTIFS to count different positions related to different departments.
If this is not really what you need, describe the outcome you'd like to get in detail.
Hi Natalia I tried using COUNTIF, but I don't know which formula to use, the details I have is like this in a spreadsheet
Column A - Employee Name
Column B - Department - IT,It, IT, IT, It, Administration, Administration
Column C- IT Assistant, IT Assistant, IT Associate, IT Manager, It Assistant, Admin Staff, Admin Staff, Admin Supervisor,
This is the outcome I want using COUNTIF with more than 50 employees
How many position in each department?
IT Department - 3
Administrative - 2
I'll give another example
Column A Fruit, Fruit, Fruit, Vegetable, Vegetable, Vegetable
Column B, Apple, Apple, Strawberry, Lettuce, Cabbage, Onion
Fruits - 2
Vegetable - 3
Hope this make sense. Thanks
Thank you for the details, JM.
In your case, you'd better use COUNTUNIQUEIFS. Sadly, we haven't described it here yet, so while we do so, you can find the details here: https://support.google.com/docs/answer/9584429?hl=en#null
Here's how your formulas may look like:
will count unique positions in column C if the Department in B is IT.
will count unique positions in column C if the Department in B is Administration.
How can I count the total number of sheets in a single Google spreadsheet?
I'm afraid there's no standard way of counting sheets within a spreadsheet. You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links.
I'm happy to let you know that recently we've released a new add-on: Sheets Manager. It's available as a separate add-on and as part of the Power Tools collection.
Among other useful features, there's a sheet count available :)
How do I count the number of times all the numbers show up in the entire sheet? I have student ID numbers that are on the sheet based on a day. I have eight days of data so a student's name may be on there multiple days. How do I count the number of times a student's ID# is on the sheet? Each day has 300+ ID#s and there is 8 columns worth of days.
If I understand your task correctly, the COUNTIF function will help you. It is designed to count the occurrence of a specific value in the range. You will learn more about the function in this blog post.
We are trying to determine the best way to split our students into two groups, by counting the first letter of their last name in each of the homerooms and then seeing which has more a-k, a-l, a-m so we have an even amount on the alternating days they will come. All the students are in one workbook spread out by grade level on ten sheets so I would need to count the first letter of the last name on all sheets. I need two counts. One with all students and then one that removes the students who are only virtual (I have a separate column with this info). I'm working in Google sheets. Thank you!
I'm so sorry for such a late reply – somehow I missed the notification about your comment.
If you're still looking for a solution, try using the COUNTIF function. We described ways to count based on multiple criteria in this part of the blog post.
Hi, How do i count number of unique values in a spreadsheet?
How can I make sure the number is updated automatically when a new value is added to the spreadsheet?
you can try using the COUNTUNIQUE function for the task. Just use entire columns (like A:C) instead of exact ranges (like A2:C100) in order to include all new entries.
I am hoping to get a count from a column. I want to be able to get a count of each different unit code in a column. So, not an overall count of the whole column but a count of each individual code in that column. I hope my question makes sense.
The quickest and simplest way would be to create a pivot table from the desired range: with the values you want to count as Rows and the COUNTA function as Values in it. If you're not familiar with pivot tables, feel free to read this blog post.
How do you count how many times a particular figure appears in the sheet or in a column/selected range?
It is COUNTIF that does that in Google Sheets.
How to count the characters in one cell?
To get the total of chars in a cell, use the LEN function:
To count all occurrences of a specific character in a cell, say, the letter "a", the formula should be like this:
HI I NEED HELP .. I NEED TO COUNT THE DATE FROM TO A DAY TODAY
Do you need to add/subtract some number of days from today's date? Or do you need to get the number of days between two days? Please describe in detail what data you have and what you're trying to do.
Can a counter be made in sheets to display the number of times a name appears on the spreadsheet
Sure. Since you know what name you need to count exactly - this is your condition. To count based on conditions, please refer to the article about the COUNTIF function.
How do I calculate the over break instance in gsheet per headcount per month?
I'm sorry but your task is not clear. Would you mind explaining it in more detail?
take a loot at this article https://www.ablebits.com/office-addins-blog/calculating-time-google-sheets/
How do i calculate total days, if the date are given from and to? please help