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

score 26 · Answer 1 · answered Jul 25 '13 at 11:55

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

Hi, is it possible to add criteria paramters? Like in the function "countifs". Thanks! — dofine, Jul 25 '18 at 10:58

score 9 · Accepted Answer · edited Jan 25 '12 at 21:17


=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

not working for me, but i've managed to make a good one out of yours. thanx. — dayan, Nov 04 '09 at 22:35
The formula is an array formula. Press `CTRL`+`SHIFT`+`ENTER` instead of just `ENTER` to save it. — ChrisB, Mar 24 '21 at 02:04

score 8 · Answer 3 · answered Nov 04 '09 at 19:47

8

Try:

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

EDIT: The above will handle blank entries in the column

answered Nov 04 '09 at 19:47

Jacob

34,255
14
110
165

1

When trying this it just gives me a 0 for the answer – Mike Apr 18 '17 at 13:28

Jim D · Answer 4 · 2014-02-04T21:24:55.620

7

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.

edited Feb 04 '14 at 21:24

answered Feb 04 '14 at 21:11

Jim D

71
1
2

score 6 · Answer 5 · answered Mar 18 '16 at 11:18

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

For everyone who didn't know: In order to reference 'Microsoft Scripting Runtime' go to your VBAProject and click Tools -> references... -> check the box 'Microsoft Scripting Runtime' — Axel, Aug 09 '17 at 07:48

score 2 · Answer 6 · answered Mar 20 '15 at 23:11

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))

score 2 · Answer 7 · edited Feb 26 '19 at 13:51

2

Look at https://excelchamps.com/blog/count-unique-values-excel/. There is your answer.

The formula you need to enter is:

=SUMPRODUCT(1/COUNTIF(C2:C2080,C2:C2080))

When you enter this formula as an array it will look something like this:

{=SUMPRODUCT(1/COUNTIF(C2:C2080,C2:C2080))}

edited Feb 26 '19 at 13:51

barbsan

3,418
11
21
28

answered Feb 26 '19 at 13:35

Kleberson Felix Silva

64
1

score 1 · Answer 8 · edited May 08 '18 at 21:49

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

score 1 · Answer 9 · answered May 30 '16 at 04:22

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

score 1 · Answer 10 · answered Apr 18 '17 at 13:29

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.

score 1 · Answer 11 · answered Jul 20 '20 at 21:54

Since fall 2018, this problem can be solved much easier using the new dynamic array functions (currently only available for Office 365 clients - this solution does not work on Excel 2016 / 2019):

=COUNTA(UNIQUE(C2:C2080))

The UNIQUE function will result in an array of unique values in the range, which can be counted using COUNTA.

If you have blanks in your range, you may filter them out using the FILTER function:

=COUNTA(UNIQUE(FILTER(C2:C2080,C2:C2080<>"")))

Exactly the answer I was going to add. Well done! – Chiramisu Apr 27 '21 at 19:54 — Chiramisu, Apr 27 '21 at 19:54

Count unique values in Excel

11 Answers11

Linked

Related