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

For anyone still trying to use @JustinG's dictionary method, you will need to alter the code slightly if you're using a newer version of VBA.

You'll need to reference 'Microsoft Scripting Runtime' and prefix the Dictionary terms with Scripting, as follows:

Public Function CountUnique(rng As Range) As Long
    Dim dict As Scripting.Dictionary
    Dim cell As Range
    Set dict = New Scripting.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

Answer 2

Try:

=SUM(IF(FREQUENCY(C2:C2080,C2:C2080)>0,1))

EDIT: The above will handle blank entries in the column

Answer 3

The formula works for me. There are a few things that could cause this to not work. First, all target cells must have a value in them. Another example of where this might not work is if you have one cell with the value 31 and another cell with a text value of "31". It will recognize these as different values.

You could try this:

=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""), IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))

This is an array formula. Instead of hitting just enter to confirm it you must hit ctrl+shift+enter.

Which is from:

http://www.cpearson.com/excel/Duplicates.aspx

Answer 4

Another way to do this is this:

Sub CountUnique()
    Dim Count, x, a, lastRow, Values(), StringValues
    a = ActiveCell.Column
    a = GetLetterFromNumber(a)
    lastRow = Range(a & Rows.Count).End(xlUp).row
    Count = 0
    For Each c In Range(Range(a & "1"), Range(a & Rows.Count).End(xlUp))
        If c.row = 1 Then
            ReDim Values(lastRow)
            Values(Count) = c.Value
            Count = Count + 1
        End If
        StringValues = Join(Values, "#")
        StringValues = "#" + StringValues
        If InStr(1, StringValues, c.Value) = 0 Then
            Values(Count) = c.Value
            Count = Count + 1
        End If
    Next c
    MsgBox "There are " & Count & " unique values in column " & a
End Sub

You just have to have the active cell be on row 1 of the column that you are counting.