Count unique values in Excel

Question

I need to count unique values in range (C2:C2080) in excel. Googled formula:

=SUM(IF(FREQUENCY(MATCH(C2:C2080;C2:C2080;0);MATCH(C2:C280;C2:C2080;0))>0;1)) 

return incorrect value.

UPD: Lame solution:

Sub CountUnique()

Dim i, count, j As Integer

count = 1
For i = 1 To 470
    flag = False
    If count > 1 Then
        For j = 1 To count
            If Sheet1.Cells(i, 3).Value = Sheet1.Cells(j, 11).Value Then
                flag = True
            End If
        Next j
    Else
        flag = False
    End If

    If flag = False Then
        Sheet1.Cells(count, 11).Value = Sheet1.Cells(i, 3).Value
        count = count + 1
    End If

Next i

Sheet1.Cells(1, 15).Value = count

End Sub

Answer 1

This might be a more efficient way of dealing with a large number of rows. This uses the inbuilt AdvancedFilter command instead of cycling through each cell at a time.

Public Function UniqueValues(oRange As Range) As Variant

' Uses the built-in AdvancedFilter Excel command to return the unique values onto the Worksheet
' and then populate and retuns an array of unique values

' Note:  The index:0 element in the returned array will be the header row.
'        You can ignore this element unless the first row in your oRange is a unique value
'        in which case the header will be that value.

Dim oTarget As Range
Dim r As Long, numrows As Long
Dim vs1, vs2 As Variant

' Get the first unused cell on the first row where the unique vaues will be temporarily populated
   Set oTarget = oRange.SpecialCells(xlLastCell) ' the last cell on the worksheet
   Set oTarget = oTarget.Parent.Cells(1, oTarget.Column + 1) ' the first unused cell on the first row

' Copy the unique values from your oRange to the first unused cell on row 1
   oRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=oTarget, Unique:=True

' Get the number of rows including the first row which is the header
   numrows = WorksheetFunction.CountA(oTarget.EntireColumn)

' create an 2-dim array of the rows
   vs1 = oTarget.Resize(numrows)

' Prepare a second 1-dim array for the result
   ReDim vs2(numrows)

' Transfer the 2-dim array into the 1-dim array
   For r = 1 To UBound(vs1, 1)
      vs2(r - 1) = vs1(r, 1)
   Next

' Return the 1-dim array as the function result
   UniqueValues = vs2

' Clean up the extra column on the worksheet
   oTarget.EntireColumn.Delete

End Function

Answer 2

JustinG's function works very well (and fast) until the number of unique items exceeds 32,767 due to some type of limit in Excel.

I found if you modify his code

Public Function CountUnique(rng As Range) As Integer

and make it as...

Public Function CountUnique(rng As Range) As Long

It will then handle more unique items.

Answer 3

You could also simply use a filter to temporarily display unique values, and count the filtered values.

Answer 4

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1)) 

http://office.microsoft.com/en-us/excel/HP030561181033.aspx

You may also write a VBA macro (not sure if that's what you're after though.)

Something to the effect of (given a spreadsheet with A1-A11 filled and B1-B11 empty):

Sub CountUnique()

Dim count As Integer
Dim i, c, j As Integer

c = 0
count = 0
For i = 1 To 11
    Sheet1.Cells(i, 2).Value = Sheet1.Cells(i, 1).Value
    c = c + 1
    For j = 1 To c
        If CDbl(Sheet1.Cells(i, 1).Value) = CDbl(Sheet1.Cells(j, 2).Value) Then
            c = c - 1
            Exit For
        End If
    Next j
Next i

' c now equals the unique item count put in the 12'th row
Sheet1.Cells(12, 1).Value = c

End Sub

Answer 5

After reading through this and then investigating further, I've got one that works better for me than anything I see here:

Array-enter:
(Ctrl+Shift+Enter, and don't include the curly brackets)

{=SUM(IFERROR(1/COUNTIF(C2:C2080,C2:C2080),0))}

Or in VBA:

MyResult = MyWorksheetObj.Evaluate("=SUM(IFERROR(1/COUNTIF(C2:C2080,C2:C2080),0))")

It works for both numbers and text, it handles blank cells, it handles errors in referenced cells, and it works in VBA. It's also one of the most compact solutions I've seen. Using it in VBA, it apparently automatically handles the need to be an array formula.

Note, the way it handles errors is by simply including them in the count of uniques. For example, if you have two cells returning #DIV/0! and three cells returning #VALUE!, those 5 cells would add 2 to the final count of unique values. If you want errors completely excluded, it would need to be modified for that.

In my tests, this one from Jacob above only works for numbers, not text, and does not handle errors in referenced cells (returns an error if any of the referenced cells returns an error):

=SUM(IF(FREQUENCY(G4:G29,G4:G29)>0,1))

Answer 6

Here is a VBA function that works for me.

You can use it as a worksheet function, referencing any range, eg “=CountUnique(N8:O9)”

It handles text and numeric values, and treats blank cells as one value

It does not require dealing with array functions.

It requires a reference to the Microsoft Scripting Library, for the dictionary object.

    Public Function CountUnique(rng As Range) As Integer
        Dim dict As Dictionary
        Dim cell As Range
        Set dict = New Dictionary
        For Each cell In rng.Cells
             If Not dict.Exists(cell.Value) Then
                dict.Add cell.Value, 0
            End If
        Next
        CountUnique = dict.Count
    End Function