To count unique text values in a range using Excel, you can use a formula with the SUM and COUNTIF functions.
How to count unique text values in a range
- Open Excel.
- 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.
Formula:
=UTEXT(data)
Finally, use the COUNT function.
Formula:
=COUNT(UTEXT(data)) = 6
To learn more about UDFs, check out our powerful functions here.
Here is the source code of the UTEXT function:
Function UTEXT(rng As Range) As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim cell As Range
For Each cell In rng
If Not dict.Exists(cell.Value) Then
dict.Add cell.Value, 1
Else
dict(cell.Value) = dict(cell.Value) + 1
End If
Next cell
Dim output() As Variant
ReDim output(1 To dict.count, 1 To 2)
Dim i As Long
For i = 1 To dict.count
output(i, 1) = dict.Keys()(i - 1)
output(i, 2) = dict.items()(i - 1)
Next i
UTEXT = output
End Function