Count unique text values in a range

To count unique text values in a range, you can use a formula with the SUM and COUNTIF functions.

How to count unique text values in a range

1. Type the =SUM(1/COUNTIF(data,data)) formula.
2. Press Enter.
3. The formula returns the number of cells that contain unique text values.

Example

First, let us see how to create a formula in Excel. In the example, the data set is in the range B3:B12. Create a named range, select the range, click the name box, and type “data”. From now on, B3:B12 refers to “data”.

Formula:

=SUM(1/COUNTIF(data,data))

Evaluate the formula from the inside out.

=COUNTIF(data, data)

The COUNTIF function counts unique values in the selected range. For example, COUNTIF(“Apple” data) returns 4, since it exists in cells B3, B4, B7, and B9.

=1/COUNTIF(data, data)

This part of the formula takes the inverse of each unique value.

The formula calculates the weighted average of the values in the range “data” and creates a frequency distribution. For example, “Apple” appears 4 times in the source range, so its weight is 1/4. On the other hand, “Banana” appears only once, so the weight is 1.

Finally, the SUM function sums the values in the result array.

=SUM({0.25 + 0.25 + 1 +1 + 0.25 + 0.5 + 0.25 + 0.5 + 1}) = 6

Count unique text values with the UTEXT function

If you like user-defined functions, you can create a pivot-style output. For example, you can use the UTEXT function to count unique text values in a range.

UTEXT is a dynamic array function; the result spills into the range, not a single cell.

Generic Formula:

=UTEXT(data)

To count unique text values, combine the function with the COUNT function.

Formula:

=COUNT(UTEXT(data)) = 6

To learn more about UDFs, check out our powerful functions here.

Related Formulas:

• Unique values with criteria
• Count unique values and distinct values