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.
It’s also possible to use COUNTIF, as explained below.
Assume you have a list of employee names together with hours worked on “Project X”, and you want know how many employees worked on that project. Looking at the data, you can see that the same employee names appear more than once, so what you want is a count of the unique names.
The employee names appear in the range B3:B12. To get a count of unique names, you can use the following formula:
How this formula works
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 array of values for the match_value argument, it returns an array of positions. These are fed to FREQUENCY in the data array argument.
The bins array argument is constructed from this part of the formula:
which uses the row number of each item in the data and the row number of the first item in the data to build a straight, sequential array like this:
The FREQUENCY function returns an array of values that correspond to “bins”. In this case, we are supplying the same set of numbers for both the data array and bins array.
The result is that FREQUENCY returns an array of values that indicate the count that each value in the data array appears. FREQUENCY has a special feature that automatically returns zero for any numbers that appear more than once in the data array, so the return array looks like this:
Next, each of these values is converted to TRUE or FALSE by the >0 construction, and then to 1 or zero with the double-unary (double-hyphen). This is done because SUMPRODUCT needs numeric values, it can’t work directly with text or logical values.
Inside the SUMPRODUCT function the final array looks like this:
Finally, SUMPRODUCT simply adds these values up and returns the total, which in this case is 4.
Handling empty cells in the range
If any of the cells in the range are empty, and you want to use FREQUENCY instead of COUNTIF, you’ll need use a more complicated formula that includes IF:
Because the logical test portion of the IF statement contains an array, the formula automatically becomes an array formula that requires Control-Shift-Enter. This is why SUMPRODUCT has been replaced with SUM.
Working inside out, the reason IF is required is because MATCH will return #N/A if the match value contains empty values. By testing for empty values with data<>””, and including MATCH as the value if true, the resulting array will contain numbers combined with FALSE:
which is supplied to FREQUENCY as the data array. FREQUENCY will then return an array like this:
The elements in this array are converted to either 1’s or FALSE with the final (outer) IF statement. The result looks like:
SUM then adds up the 1’s and returns 4.
Using COUNTIF instead of FREQUENCY to count unique values
Another way to count unique numeric values is to use COUNTIF instead of FREQUENCY. This is a much simpler formula, but beware that using COUNTIF on larger data sets to count unique values can cause performance issues. The FREQUENCY-based formula, while more complicated, calculates much faster.