Posted on

Count unique values in a column in Excel

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.

PlayerTeamPosGamesABRunsHits2B3BHRRBI
 Posey, BSFC7125735871701035
 Murphy, DWSH2B77303541012521455
 Gamel, BSEARF602334277152427
 Judge, ANYYRF7928272931332762
 Zimmerman, RWSH1B7628250932201962
 Altuve, JHOU2B81310531012321140
 Ramirez, JCLE3B8030559992641542
 Dickerson, CTBDH79324601042131740
 Cozart, ZCINSS612284073165933
 Correa, CHOUSS7629856951711758
 Garcia, ACWSRF7528940921731151
 Harper, BWSHRF7628663912002062
 Votto, JCIN1B8229361931902461
 Goldschmidt, PARI1B8229471931921966
 Peralta, DARIRF732664884162826
 Ozuna, MMIALF8131249981402262
 Bogaerts, XBOSSS773034995204539
 Castro, SNYY2B7329452921311245
 Blackmon, CCOLCF833426710717101859
 Hosmer, EKC1B8231344971811138

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.