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.


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”.



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.

create weights using COUNTIF

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:

Count unique text values with the UTEXT function

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


=COUNT(UTEXT(data)) = 6

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

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.