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.

The following tutorials explain how to perform other common tasks in Excel:

5 Replies to “How to Count Number of Occurrences in Excel”

1. N says:

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

2. Jody Hatton says:

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

3. Christopher says:

“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.

4. Alan B says:

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

5. Elizabethe says:

My Excel does not list a formula called UNIQUE