# How to Count Unique Values by Group in Excel

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.