# 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`` 