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.

**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.