Table of Contents
To count the number of occurrences in Excel, use the COUNTIF function. This formula searches a range of cells for a particular value and returns the number of times it appears. This function is especially useful when analyzing large data sets, as it enables you to quickly get a count of how many times certain values appear in the data.
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.