How to count unique/distinct users within a data set

Hi there,

I'm trying to count the number of distinct users from a data set.

For example: if I have three teams, team 1, team 2, and team 3 and I have 8 rows of data for team 1, 3 rows of data for team 2, and 1 row of data for team 3 but I can see a user has been entered multiple times on team 1 and team 2. How do I count the number of unique/distinct users for every team?

Capture.PNG

Results should be
Team 1 has 5 unique/distinct users
Team 2 has 2 unique/distinct users
Team 3 has 1 unique/distinct users

I've tried countif formula's but that only gets me a total amount of data point per team and not the number of distinct users. I've also tried a frequency formula but that can't count based on specific teams and only show the frequency of all the data. Not sure if there is a way to merge the two formulas to achieve this goal.

Also, trying to stay away from array formulas as my spreadsheets can be > 100,000 data points and that makes my Excel crash and staying away from pivot tables.



Thanks so much!