# Count number of unique codes in a column

#### rob51852

##### Board Regular
Hi, I have a spreadsheet which contains a list of 70,000 numeric codes in column B. Some of the codes are duplicated multiple times.

Is there a formula that will count the number of unique codes?

Thanks

#### James006

##### Well-known Member
Hi,

You can use following formula

=SUMPRODUCT(1/COUNTIF(yourRange,yourRange))

Hope this will help

#### rob51852

##### Board Regular
Hi,

You can use following formula

=SUMPRODUCT(1/COUNTIF(yourRange,yourRange))

Hope this will help
Hi James,

Thanks. The formula just returns a 0... Have I transposed it wrong:

=SUMPRODUCT(1/COUNTIF(D2:D61932,D2:D61932))

#### James006

##### Well-known Member
Hi,

Apparently 70'000 records is too much for Excel to swallow ...

Would suggest to Insert a Pivot Table ... much safer ...

#### rob51852

##### Board Regular
Hi,

Apparently 70'000 records is too much for Excel to swallow ...

Would suggest to Insert a Pivot Table ... much safer ...
Thanks. Just came back to my PC and it's done it: 2152

Thanks again

#### James006

##### Well-known Member
Glad you could fix your problem ...

#### mumps

##### Well-known Member
Assuming you have headers in row 1, try this macro:
Code:
``````Sub CountUniques()
Application.ScreenUpdating = False
Dim LastRow As Long, rowCount As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
rowCount = [subtotal(103,B:B)] - 1
MsgBox ("There are " & rowCount & " unique values.")
Range("B1").AutoFilter
Application.ScreenUpdating = True
End Sub``````