Excel Formula Count the Number of Unique Values
Anyone who has worked a number of Excel spreadsheets produced in electronic discovery has dealt with worksheets with repetitive entries in hundreds or thousands of cells. If you want to know the number of unique values in a column you could simply use the remove duplicates tool on the Data ribbon, but if you don't want to disrupt the data on the worksheet or bother copying it to another draft spreadsheet there's another option. Enter this formula referring to the complete range of data in the column you want to get a count of unique values in.
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1
Be sure to enter the specific numbers of the beginning and ending rows rather than just referencing the whole column as 'A:A'.
In the example below we want to know how many seasons are referenced in column D of this spreadsheet showing fielding data for major league baseball players. So we enter the formula this way:
=SUM(IF(FREQUENCY(D1:D59885,D1:D59885)>0,1))
. . . and as you can see the result is that 124 seasons of baseball are recorded on the spreadsheet.
Microsoft provides an explanation of the formula on this web page: