You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Excel.
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, let’s enter the names for a list of 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:
This function creates an array of unique values by default.
Step 3: Count the Occurrence of Each Unique Value
Next, let’s use the following formula to count the number of occurrences of each unique team name:
=COUNTIF($A$2:$A$15, D2)
The following screenshot shows how to use this formula in practice:
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 2 times in column A.
- The team name ‘Mavs’ occurs 3 times in column A.
- The team name ‘Spurs’ occurs 3 times in column A.
- The team name ‘Rockets’ occurs 1 time in column A.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Duplicates in Excel
How to Count Frequency of Text in Excel
How to Count by Group in Excel