In this tutorial, we will learn How to count number of Occurrences in Google Sheets
To count the number of occurrences in Google Sheets we can use the =UNIQUE() and =COUNTIF() functions
How to Count Number of Occurrences in Google Sheets: Step by Step Guide
Enclosed are the steps which you can follow to count the number of occurrences in Google Sheets.
Step 1: Open Google Sheets
Open your Google Sheets with data where you want to calculate the number of occurrences in a Column in Google Sheets
In this example, we have a Google Sheet with a list of Football teams where we will calculate the number of occurrences.
Step 2: Calculate the Unique value with the UNIQUE function
Now we will calculate the unique value using the UNIQUE function.
SYNTAX
UNIQUE(range)
- Navigate to the cell where you want to get the unique value in our example C2
- Now type the formula in the cell =UNIQUE(A2:A17) and press enter.
- Here we have specified the range from cell A2 to A17, you can type the range as per your requirement.
Once we the UNIQUE function for the specified range it automatically calculates the unique teams for the specified range.
Step 3: Use the COUNTIF function to get the Unique occurrences
Now we will count the unique occurrence of Unique teams using the =COUNTIF() function
Syntax
COUNTIF(range, criterion)
- Range: Here we need to specify a range for example A2-A17
- Criterion: Here we need to specify the Cell number to apply a test or pattern in our example from Unique teams
- Navigate to the cell where you need to calculate the count of unique occurrences.
- Type the formula =COUNTIF($A$2:$A$17, C2), adjust the formula as per your data
Now press Enter,
Google Sheets auto-suggest will ask to automatically apply the formula on the remaining cell, Click on the checkmark
Now the formula is applied to the rest of the cells and we have the count for the rest of the Unique teams
Count Number of Occurrences with Partial Match
There might be a case where you need to count a cell with a Partial Match however the COUNTIF function can only count an exact match.
To solve this issue we will use an asterisk (*) wildcard which is interpreted as a partial match by Google Sheets.
SYNTAX
=COUNTIF(range,”*”&criterion&”*”)
- range – This is the range for which Google Sheets will perform the count
- criterion – The pattern or test that should be applied to the range.
In the following example screenshot, we have used the below formula
=COUNTIF($A$2:$A$17,"*"&C2&"*")
- Navigate to the cell where you need to calculate the count of unique occurrences with Partial Match.
- Type the formula =COUNTIF($A$2:$A$17,”*”&C2&”*”), adjust the formula as per your data
Wrapping up
Using the preceding step-by-step guide you can easily get the unique occurrences and count with a few simple steps.
We hope this tutorial on How to Count Number of Occurrences in Google Sheets was helpful.
Related articles :
How To Transpose Data in Google Sheets
How to count non-empty cells in google sheets