2

Below is an VBA function to calculate unique values count (Credit to SO: Count unique values in Excel) is it possible to add criteria paramaters? Like in the function "countifs"?

   Public Function CountUnique(rng As Range) As Integer
    Dim dict As Dictionary
    Dim cell As Range
    Set dict = New Dictionary
    For Each cell In rng.Cells
         If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, 0
        End If
    Next
    CountUnique = dict.Count
End Function
Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
dofine
  • 863
  • 1
  • 8
  • 20
  • 2
    It depends on criteria. But probably you can play with the line that says `If Not dict.Exists(cell.Value) Then` adding your conditions there. Something like `If Not dict.Exists(cell.Value) And cell.value > Parameter1 And cell.value < Parameter2 Then...` Just an example – Foxfire And Burns And Burns Jul 25 '18 at 11:01

1 Answers1

2

This is what I have created:

enter image description here

The code looks like this:

Public Function CountUnique(inputRange As Range, paramRange As Range) As Long

    Dim dict As New Dictionary
    Dim cellInput As Range
    Dim cellParam As Range
    Dim keepLooking As Boolean

    For Each cellParam In paramRange
        keepLooking = True
        For Each cellInput In inputRange
            If cellParam.Value2 = cellInput.Value2 And keepLooking Then
                If Not dict.Exists(cellInput.Value2) Then
                    dict.Add cellInput, 0
                    keepLooking = False
                End If
            End If
        Next
    Next
    CountUnique = dict.Count

End Function
Vityata
  • 42,633
  • 8
  • 55
  • 100