How to get Unique Count in Excel

Rohit Raj
2 min readSep 12, 2024

--

If like me you are stuck with Excel version<2021, then getting distinct counts in a range is an never-ending challenge.

For just getting unique values in a single column this article lists out several ways to get unique count

But if you want to use a formula to get unique count in range where we want to filter based on certain conditions. Then there are no good solutions to this in the abovementioned article. Even ‘UNIQUE’ formula in Excel 2021 is not sufficient.

I wanted the flexibility of Excel SUMIFS formula. I want the formula to return discount count in a range where adjacent columns satisfy given conditions.

For this, I have written the following macro

Function CountUniqueConditions(rngValues As Range, ParamArray criteriaRangesAndValues() As Variant) As Long
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim i As Long, j As Long
Dim valueKey As String
Dim resultCount As Long
Dim criteriaRange As Range
Dim criteriaValue As Variant
Dim allCriteriaSatisfied As Boolean

resultCount = 0

' Loop through the range of values
For i = 1 To rngValues.Count
allCriteriaSatisfied = True

' Loop through the criteria and check if each is satisfied
For j = LBound(criteriaRangesAndValues) To UBound(criteriaRangesAndValues) Step 2
Set criteriaRange = criteriaRangesAndValues(j)
criteriaValue = criteriaRangesAndValues(j + 1)

' Check if the current criteria is satisfied
If criteriaRange.Cells(i, 1).Value <> criteriaValue Then
allCriteriaSatisfied = False
Exit For
End If
Next j

' If all criteria are satisfied (or no criteria given), process the value
If allCriteriaSatisfied Then
valueKey = CStr(rngValues.Cells(i, 1).Value)

' If this value is not yet in the dictionary, add it
If Not dict.exists(valueKey) Then
dict.Add valueKey, True
resultCount = resultCount + 1
End If
End If
Next i

' Return the count of unique values
CountUniqueConditions = resultCount
End Function

Saved this VBA macro in your Excel workbook and enable macros in your Excel. Then you can use this macro function to get unique counts in the following manner.

  1. No criteria (just counting unique values):
=CountUniqueConditions(A2:A10)

If you don't give any additional conditions then it would return a distinct count in your range.

2. One criterion (column B should match “criteria1”):

=CountUniqueConditions(A2:A10, B2:B10, "criteria1")

In a similar format as SUMIFS formula, you can give a condition for the adjacent column, it will return a distinct count in A2:A10 only where values in B2:B10 equals “criteria1”.

3. Multiple criteria (as many as needed):

=CountUniqueConditions(A2:A10, B2:B10, "criteria1", C2:C10, "criteria2", D2:D10, "criteria3")

You can give as many criteria as needed.

If you like this formula then please clap and subscribe.

--

--

Rohit Raj

Studied at IIT Madras and IIM Indore. Love Data Science