In this article you will learn how to count unique values present in Excel list or range. I will show you various ways to output unique values count using array function, VBA UDF and pivot table.

Unique and Distinct values definition

Before going further I want to clarify the difference between Unique values and Distinct values with respect to this article.  In general count and distinct terms used interchangeably.

Uniqe values – These are non duplicate values which appeared single times in a range or list. In the below image we have 3 unique values.

Distinct values – These are values obtained when duplicates (2nd or more instances of a value) are removed from a list or range. In the below image we have 5 distinct values.

Count unique and distinct values using helper columns

This is most straightforward and easy to understand method.

If you want to count Unique values then build helper column for total count of each values using Countif function. 

If you want to count Distinct values then build helper column for running count of values using Countif function.

Cell C3 formula to count each values in a range =COUNTIF($B$3:$B$9,B3)

Cell D3 formula for running count of values =COUNTIF($B$3:B3,B3) .Make sure to make only 1st cell of a list range absolute.

 Cell G2 formula for unique count of values =COUNTIF(C3:C9,1)

Cell G3 formula for Distinct count of values =COUNTIF(D3:D9,1)

Count unique and distinct values using array formula

Cell E2 formula for counting unique values =SUM(IF(COUNTIF(B3:B9,B3:B9)=1,1,0))

In the below image you can see curly brackets around formula which means it is an array formula. So you have to press CTRL+SHIFT+ENTER key instead of just ENTER key to make it an array one after writing formula.

Unique values count formula working

COUNTIF(B3:B9,B3:B9) array formula will give array of count of values. To see evaluation just select COUNTIF(B3:B9,B3:B9) section then press function key F9.

You will see something like below 

=SUM(IF({1;1;2;2;1;2;2}=1,1,0))

These {1;1;2;2;1;2;2} array values are evaluated by If function to produce new array like this =SUM({1;1;0;0;1;0;0}). Select IF(COUNTIF(B3:B9,B3:B9)=1,1,0) section then press F9 to see evaluation.

Now Sum functions just sums the array values evaluated by If function.

Cell E3 formula for distinct count =SUM(IFERROR(1/COUNTIF(B3:B9,B3:B9),0))

This is also an array formula so press CTRL+SHIFT+ENTER to make an array formula.

Distinct values count formula working

COUNTIF(B3:B9,B3:B9) array formula will give array of count of values. To see evaluation just select COUNTIF(B3:B9,B3:B9) section then press function key F9.

=SUM(IFERROR(1/{1;1;2;2;1;2;2},0))

Now this section 1/{1;1;2;2;1;2;2} , 1 will be divided by each array values to give new array like this {1;1;0.5;0.5;1;0.5;0.5}. This 1 divided by count of array values is the soul of this formula. 

IFERROR is there only for handling divide by zero error in case of blank cells.

Now sum just sums the array values {1;1;0.5;0.5;1;0.5;0.5} to output distinct count.

Mathematically one of the way to convert a number (say 4)  into 1 is by dividing 1 by that number (4) then summing output (0.25) by number of times (4) which is equal to that number (4).

Count unique and distinct text values

Unique text values count cell E2 formula =SUM(IF(ISTEXT(B3:B11)*COUNTIF(B3:B11,B3:B11)=1,1,0)) 

Do not forget to press CTRL+SHIFT+ENTER to make it array formula to complete it.

Basic working is same as the above section ,we are multiplying with array of True or False (1 or 0) outputted by ISTEXT(B3:B11) section. 

1 or True is there for text value 0 or False is there for non-text values.

=SUM(IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}*{1;1;2;2;1;1;1;2;2}=1,1,0))

After product  =SUM(IF({1;0;0;2;0;0;1;2;0}=1,1,0))

cell E3 formula for distinct count text only only =SUM(ISTEXT(B3:B11)*IFERROR(1/COUNTIF(B3:B11,B3:B11),0))

Above is array formula so press CTRL+SHIFT+ENTER for completing it.

Count unique and distinct number values

cell E2 array formula =SUM(IF(ISNUMBER(B3:B11)*COUNTIF(B3:B11,B3:B11)=1,1,0))

Note:- Date and time in Excel treated as Number.

cell E3 array formula =SUM(ISNUMBER(B3:B11)*IFERROR(1/COUNTIF(B3:B11,B3:B11),0))

How to count unique values using Pivot Table

Note:- This option is available in Excel version 2013 and above.

1- Select range with heading then click on Insert tab select Pivot Table. Choose whether you want pivot on same sheet or on New worksheet.

Tick on Add this data to the Data Model option. Click on Ok button.

2- Drag column name to Values section of Pivot Table Fields list. Click on dragged field present in Values section then click on Value Field Settings.

3- In Value Field Settings select Distinct Count , in Custom Name you can enter your own text instead default one. Click on Ok button.

After click Ok button Distinct values count will shown.

If you add or change values in the list then just update pivot data source and refresh pivot table to get updated distinct values count.

VBA UDF to count unique values and distinct values

This single VBA UDF will replace all of the above complex array formulas. 

Using this VBA UDF you can count unique values, count distinct values, count unique text values, count unique number values and count distinct text and number values.

This VBA UDF formula is more user-friendly as compared to array formula method since VBA UDF hides the implementation at the back end.

Below is UDF code section. This UDF is optimized for speed and I have tested over 50 thousand rows. Even for 50 thousand rows it just outputs value in the blink of eye time (less then 1 sec).

Note:- Don’t be afraid of long code and not to worry about code working. Just do as said below. It is very easy.

To create below UniqueDistCount UDF press ALT+F11 to open VBA IDE. In VBA IDE go to Insert tab and click on Module to create new standard module. Now copy paste below code section in that module.

Make sure to save as your workbook either in .xlsb (binary) or .xlsm (macro) format.

Make sure to declare Option Compare Text at the very top before any sub or function.

Option Compare Text
Function UniqueDistCount(rng As Range, _
    Optional cntType As String = "d", Optional dataType As String = "") As Long
    'cntType input "u" for unique count, "d" for distinct count as default
    'dataType input "t" for text,"n" for numbers,any data type leave blank
    Dim rngAr As Variant
    If rng.Columns.Count = 1 Then
       rngAr = Application.Transpose(rng.Value)
    Else
       rngAr = Application.Transpose(Application.Transpose(rng.Value))
    End If
    UniqueDistCount = ListCount(rngAr, cntType, dataType)
End Function
Private Function ListCount(rngAr As Variant, _
cntTyp As String, dtaType As String) As Long
  Dim val As Variant, dupValAr() As Variant, idx As Long
  Dim i As Long, cnt As Long
  i = 1:  cnt = 0
  For Each val In rngAr
    If dataTypeMatched(val, dtaType) = False Then GoTo jmp
    If IsEmpty(val) Or val = "" Then GoTo jmp
     On Error Resume Next
     idx = Application.Match(val, dupValAr, 0)
     On Error GoTo 0
     If idx > 0 Then
        idx = 0
        GoTo jmp
     End If
     If duplicate(rngAr, val) Then
       ReDim Preserve dupValAr(1 To i)
       dupValAr(i) = val
       i = i + 1
       If cntTyp = "d" Then cnt = cnt + 1
     Else
      cnt = cnt + 1
     End If
jmp:
  Next val
  ListCount = cnt
End Function
Private Function duplicate(ar As Variant, v As Variant) As Boolean
  Dim val As Variant, fstIdx As Long, i As Long, eleCnt As Long
  eleCnt = UBound(ar)
  fstIdx = Application.Match(v, ar, 0)
  duplicate = False
  For i = fstIdx + 1 To eleCnt
     If ar(i) = v Then
       duplicate = True
       Exit Function
     End If
  Next i
End Function
Private Function dataTypeMatched(val As Variant, dtaType As String) As Boolean
 'dataType input "t" for text,"n" for numbers,any data type leave blank
 Select Case dtaType
  Case "n"
    dataTypeMatched = Application.WorksheetFunction.IsNumber(val) Or IsDate(val)
  Case "t"
    dataTypeMatched = Application.WorksheetFunction.IsText(val) And Not (IsDate(val))
  Case Else
    dataTypeMatched = True
 End Select
End Function

After copy pasting UDF in Module you are ready to use UniqueDistCount UDF function in Excel cell.

 From the below image you can see on single UDF UniqueDistCount  does all types of unique values and distinct values count.It can do Unique values count overall, unique text values count, unique number values count.

Also ,it can do Distinct values count overall, distinct text values count, distinct number values count.

It takes 3 parameters or inputs

1st is range in which values are present

2nd input is optional (default value is “d” for distinct count) put “u” if you want to count Unique values from list

3rd input is optional (default value is all data type) put “t” if you want to get Unique or Distinct values count of text / string only. Put “n” if you want to get Unique or Distinct values count of numbers only.

This UniqueDistCount VBA UDF can also do Unique and Distinct values count if list is horizontally arranged.

You may also like to read the below topics

MS Excel: Count Number of Words in a String (Words Count in a Cell)

Sum and Count by Color (Background color and font color) in Excel

 

How to count unique values in Excel (count unique or distinct occurrences -2 ways) Hindi speech

https://youtu.be/OjHutagcf1g