Count unique values

 1999-08-14    Count    1    62

The custom function below counts the unique values only from a given range. This function uses the fact that a collection can only hold items with unique identifications keys. If you try to add an item with a key that already exists in the collection, an error will occur.

Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
    Application.Volatile
    On Error Resume Next ' ignore any errors
    For Each cl In InputRange
        UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
    Next cl
    On Error GoTo 0
    CountUniqueValues = UniqueValues.Count
End Function
Example, count the unique values in the range A1:A100: =CountUniqueValues(A1:A100)


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

Henrik | 2009-11-07 23:07:48 (GMT)

You can also count unique values like this:

{=SUM(1/(COUNTIF(A1:A9,"="&A1:A9)))}