Distinct Count in Excel

The most Powerful tool (arguably) in Excel …Pivot Tables… do not allow the Distinct Count of a Field …..(at least not up till Excel version 2016).

The picture below shows this

Fortunately there are some alternative solutions

1) Use POWER PIVOT

Power Pivot, unfortunately is not available in all the versions of Excel. Check this article by Microsoft which explains Which versions of Office include Power Pivot.

With Excel 2016, just add your Data to the Data Model using “Power Pivot” Tab

Now you can Insert Pivot Tables using the “Data Model” instead of your Data Table in Excel. This is depicted below

With the Pivot Table created using Workbook’s Data Model (a.k.a  Power Pivot Database), you will see the option to perform Distinct Count. This is depicted in the picture below

2) Create your Own Function … User Defined Function using VBA

Function distinctcount(Countrange As Range, Lookuprange As Range, LookUpValue As String) As Integer
Dim mycollection As New Collection
On Error Resume Next
Set mycollection = New Collection

For i = 1 To Countrange.Cells.Count
 If Lookuprange.Cells(i, 1) = LookUpValue Then
 mycollection.Add Countrange.Cells(i, 1), CStr(Countrange.Cells(i, 1))
 End If
Next i

distinctcount = mycollection.Count
End Function

Click here to Download Excel file and see the application of Power Pivot and Custom Function to compute the distinct count