Author: Oscar Cronquist Article last updated on January 05, 2019

This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one distinct value and you have created a unique distinct list.

Formulas are sometimes too slow if you work with a lot of data, this article demonstrates a user defined function you can use, however, I highly recommend using a pivot table for this task:
Count unique distinct values using a pivot table

Formula in cell H2:

=CountUniqueValues(A1:F3000)

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 add the user defined function to your workbook

1. Press Alt-F11 to open the visual basic editor
2. Press with left mouse button on 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

Get the Excel file


count-unique-distinct-dates-large-data-set.xls

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles