How to Count Unique Values by Group in Excel

by Tutor Aspire February 17, 2016

You can use the following formula to count the number of unique values by group in Excel:

=SUMPRODUCT(($A$2:$A$13=A2)/COUNTIFS($B$2:$B$13, $B$2:$B$13, $A$2:$A$13, $A$2:$A$13))

This formula assumes that the group names are in the range A2:A13 and the values are in the range B2:B13.

The following example shows how to use this formula in practice.

Example: Count Unique Values by Group in Excel

Suppose we have the following dataset that shows the points scored by basketball players on various teams:

Now suppose we’d like to count the number of unique points values, grouped by team.

To do so, we can use the =UNIQUE() function to first create a list of the unique teams. We’ll type the following formula into cell D2:

=UNIQUE(A2:A13)

Once we press enter, a list of unique team names will be displayed:

Now we can type the following formula into cell E2 to count the number of unique points values for the Lakers:

=SUMPRODUCT(($A$2:$A$13=D2)/COUNTIFS($B$2:$B$13, $B$2:$B$13, $A$2:$A$13, $A$2:$A$13))

We’ll then drag this formula down to the remaining cells in column E:

That’s it!

Column D displays each of the unique teams and column E displays the count of unique points values for each team.

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