How to get Unique Count in Excel

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

Count unique and distinct values in Excel with formula or pivot table

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.