How to Count Number of Occurrences in Excel

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