Counting unique values given criteria

[philozelda]

I'm trying to come up with a formula that will count the number of unique values, given a team and the dates of games played.

I have teams in Column A and dates in column K.

As an example, Team ATL has a total of 105 rows for every player on its team who has played a game since the start of the season. If I visually count the unique number of games using the date column (K), I count 10 games so far. I'm trying to get that count in a formula. I'm not concerned about a date range, if that helps.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
2	Team1	1/1/2014	------	Team1	3
3	Team1	1/1/2014		Team2	3
4	Team1	1/1/2014		Team3	1
5	Team1	5/10/2014
6	Team1	7/4/2014
7	Team1	7/4/2014
8	Team1	7/4/2014
9	Team1	7/4/2014
10	Team2	1/1/2014
11	Team2	3/13/2014
12	Team2	6/27/2014
13	Team3	8/4/2014
14	Team3	8/4/2014
15	Team3	8/4/2014

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$15=D2,B$2:B$15),B$2:B$15),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[philozelda]

Awesome, thx Tony

[Tony Valko]

You're welcome. Thanks for the feedback!