Posted on July 4, 2017

Count unique values in a column in Excel

In this example, we want to know how many different teams have players in the top 20.

Player	Team	Pos	Games	AB	Runs	Hits	2B	3B	HR	RBI
Posey, B	SF	C	71	257	35	87	17	0	10	35
Murphy, D	WSH	2B	77	303	54	101	25	2	14	55
Gamel, B	SEA	RF	60	233	42	77	15	2	4	27
Judge, A	NYY	RF	79	282	72	93	13	3	27	62
Zimmerman, R	WSH	1B	76	282	50	93	22	0	19	62
Altuve, J	HOU	2B	81	310	53	101	23	2	11	40
Ramirez, J	CLE	3B	80	305	59	99	26	4	15	42
Dickerson, C	TB	DH	79	324	60	104	21	3	17	40
Cozart, Z	CIN	SS	61	228	40	73	16	5	9	33
Correa, C	HOU	SS	76	298	56	95	17	1	17	58
Garcia, A	CWS	RF	75	289	40	92	17	3	11	51
Harper, B	WSH	RF	76	286	63	91	20	0	20	62
Votto, J	CIN	1B	82	293	61	93	19	0	24	61
Goldschmidt, P	ARI	1B	82	294	71	93	19	2	19	66
Peralta, D	ARI	RF	73	266	48	84	16	2	8	26
Ozuna, M	MIA	LF	81	312	49	98	14	0	22	62
Bogaerts, X	BOS	SS	77	303	49	95	20	4	5	39
Castro, S	NYY	2B	73	294	52	92	13	1	12	45
Blackmon, C	COL	CF	83	342	67	107	17	10	18	59
Hosmer, E	KC	1B	82	313	44	97	18	1	11	38

Entering the formula =SUM(IF(FREQUENCY(MATCH(B2:B21,B2:B21,0),MATCH(B2:B21,B2:B21,0))>0,1)) into a empty cell tells Excel to look in cells B2 through B21 and count the number of different or unique teams that appear in column B.