Formula to count unique text values in a range

Suppose you want to know how many students in a class have the same name. Have two ways can help do that.

Method 1: Using FREQUENCY, MATCH, ROW, and SUMPRODUCT function.

If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT.



Name Student ID
1 Noah SE04171
2 Liam SE04171
3 Olivia SE04172
4 Sophia SE04173
5 Alexander SE03669
6 Charlotte SE03678
7 Alexander SE04171
8 Olivia SE03576
9 Alexander SE03576

Try formula: =SUMPRODUCT(--(FREQUENCY(MATCH(A1:A9,A1:A9,0),ROW(A1:A9)-ROW(A1)+1)>0))

This formula is more complicated than a similar formula that uses FREQUENCY to count unique numeric values because FREQUENCY doesn’t work with non-numeric values. As a result, a large part of the formula simply transforms the non-numeric data into numeric data that FREQUENCY can handle.

Working from the inside, the MATCH function is used to get the position of each item that appears in the data. Because MATCH only returns the position of the “first match” values that appear more than once in the data return the same number.

Because MATCH receives and the array of values for the match_value argument, it returns an array of positions. These are fed to FREQUENCY in the data array argument.

Method 2: Using COUNTIF function

You can use a formula that uses COUNTIF and SUMPRODUCT instead FREQUENCY function with this formula: =SUMPRODUCT(1/COUNTIF(range,range)).

Try formula: =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) for example above and compare with result of method 1