Author: Oscar Cronquist Article last updated on January 12, 2018


This blog post describes how to count unique distinct values in list.

We have created excel formulas before to accomplish this task:

The difference from previous posts is today we are working with a large dataset. Formulas in previous posts are too slow.

What is an unique distinct list?

Remove all duplicates from a list and you have created an unique distinct list.

Example - Count unique distinct values in a large dataset

Formula in cell H2:

=CountUniqueValues(A1:F3000) + ENTER

Excel user defined function

Public Function CountUniqueValues(rng As Variant) As Variant

Dim Test As New Collection
Dim Value As Variant

rng = rng.Value

On Error Resume Next
For Each Value In rng

If Len(Value) > 0 Then Test.Add Value, CStr(Value)

Next Value
On Error GoTo 0

CountUniqueValues = Test.Count

End Function

How to implement user defined function in excel

1. Press Alt-F11 to open visual basic editor
2. Click Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor
5. Select a sheet
6. Select a cell range
7. Type =CountUniqueValues(A1:F3000) into formula bar and press ENTER

Download excel example file

count unique distinct dates large data set.xls (675 KB)

(Excel 97-2003 Workbook *.xls)