You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Google Sheets.
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, let’s enter the names for some professional basketball teams in column A:
Step 2: Find the Unique Values in the Column
Next, let’s use the =UNIQUE() function to create a list of every unique team name in column A:
Note that this function creates an array of unique values by default.
Step 3: Count the Occurrence of Each Unique Value
Next, let’s use the =COUNTIF() function to count the number of occurrences of each unique team name:
Note that we simply copy and pasted the formula in cell E2 to each of the remaining cells in column E.
From the output we can see:
- The team name ‘Hornets’ occurs 1 time in column A.
- The team name ‘Hawks’ occurs 3 times in column A.
- The team name ‘Spurs’ occurs 5 times in column A.
And so on.
The following tutorials explain how to perform other common tasks in Google Sheets: