Count unique text values in a range

count-unique-text-values-in-a-range-excel-formula

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

  1. Open Excel.
  2. Type the =SUM(1/COUNTIF(data,data)) formula.
  3. Press Enter.
  4. 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.

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.

Formula:

=UTEXT(data)

Count-unique-text-values-with-the-UTEXT-function

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