If you want to count how many times a value or word appears in a column in Google Sheets, that can be done pretty easily with a couple of functions.
You can set up a formula to tally a series of values or you can count the frequency of a single word or value depending on what you want to accomplish.
In this tutorial, I will show you how to count the number of occurrences in a column in Google Sheets.
Table of Contents
Counting Occurences in a Column
One way that we can count all the occurrences of a value in a column is to first extract the unique values using the UNIQUE function and then count the occurrence of each value using the COUNTIF function.
To show you how to do this, I will be counting the number of times each name occurs in this example dataset:
I am going to walk you through how to pull out the unique occurrences of each value from in the example data above, and then show you how to count the occurrences of each value in the original dataset.
Here are the steps:
1. The first thing we need to do is return the unique values from our data set. Select the cell where you want to begin returning these unique values and press the equals sign (=) on your keyboard. Type “UNIQUE” and press Tab to begin entering into the formula. Put the entire range you want to return unique values for into the function and add a closing parenthesis “)” when you are done.
2. Press Enter on your keyboard and you will have returned each unique value from your original list
3. Now we need to set up our COUNTIF formula to count the number of times each value appears in our original list. Select the cell where you want to count these values and press the equals sign (=) on your keyboard. Press Tab when you are done to begin entering into the formula.
4. The first argument you need to enter into this function is the original list that we are counting from. Put the entire range into the formula and press F4 on your keyboard to ensure your range is taking absolute values. Add a comma when you are done with this step
5. Next you select the first value to count from the range selected as the first argument. Select the cell that contains the value to count. Add a closing parenthesis when you are done “)”
6. Press Enter on your keyboard and your formula will calculate. You can copy and paste this formula down for each additional value that you want to count the frequency of from your original list.
Here is the complete dataset with the formulas showing
Using this formula will count the number of times each value appears in the list. So if you want to quickly calculate the frequency that a value occurs in a column, this is a good option.
There is one drawback to this though. COUNTIF will only count a cell if the entire cell’s contents are an exact match.
Watch what happens when I add more than one word into the cells that we are counting from:
You can see in the example above, now the COUNTIF formula is returning a count of zero for cells that have more than one value in them. Even if the cell is a partial match, the formula will still return zero.
In the next section, I will show you how to get around this by counting the occurrences of a value even if the cell is only a partial match.
Counting Occurrences Partial Match
As I discussed in the previous section, the one issue with COUNTIF is that it will only return a count of cells that are an exact match. If you want to count the number of times a value occurs where the cell is only a partial match, we have to make some edits to our formula.
To do this we will be using the asterisk (*) wildcard to tell Google Sheets that we want to match any instance of our value in a cell
To do this we will be using this syntax with our COUNTIF function
=COUNTIF(range,”*”&criterion&”*”)
- range – this is the range of data we are counting from
- criterion – this is the value that we are counting the number of occurrences for
Here is the formula set up inside our previous data
The exact formula I used in this spreadsheet is:
=COUNTIF($A$2:$A$11,“*”&C2&“*”)
This formula will tell Google Sheets to search for the value that I have in C2 with any character before or after the value. This is what the asterisk wildcard (*) does.
If you want to learn more about this, read my guide on how to use wildcards in Google Sheets.
Closing Thoughts
There are many different ways to count the number of times values appear in your spreadsheet. In this post, I have shown how to count when your cells are an exact match, and how to count when your cells are a partial match.
There are certainly other ways to accomplish this besides these methods, but these are two of the simplest.
As always, try to replicate these formulas in your own spreadsheet to gain a firm understanding of how these formulas work.
More Google Sheets Tutorials:
How to Count Unique Values
Using the FREQUENCY Function
How to Count Empty Cells