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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *