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

- Type the =SUM(1/COUNTIF(data,data)) formula.
- Press Enter.
- 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**.