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

booo, “=unique()” is not a published function yet..

To anyone who can’t seem to make this work — this command is only available in Microsoft 365 and Excel 2021.

“Note that we simply copy and pasted the formula in cell E2 to each of the remaining cells in column E.”

If I do this, I get just the result from D2 for all. Is there a way to include all unique team names in one formula? I’m trying to do this with questions asked by individuals. I have over 500 questions asked by 300+ individuals. I’d like to determine how many each person asked.

Love this! So simple. I had my analysis of hundreds of lines of Excel done in 1 minute. Thanks

My Excel does not list a formula called UNIQUE