Hi everyone,
I am working on a UDF that will basically function as a "countifs" but only counting unique values. I think I am on the right track, however I can't get the "EVALUATE" function to operate properly on:
Code
Msgbox Evaluate("=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))")
This is the full code:
Code
Function CountUniqueMultiple(SearchStr1 As Variant, SearchRng1 As Range, _
ReturnRng As Range, _
Optional SearchStr2 As Variant, Optional SearchRng2 As Range, _
Optional SearchStr3 As Variant, Optional SearchRng3 As Range, _
Optional SearchStr4 As Variant, Optional SearchRng4 As Range, _
Optional SearchStr5 As Variant, Optional SearchRng5 As Range, _
Optional SearchStr6 As Variant, Optional SearchRng6 As Range, _
Optional SearchStr7 As Variant, Optional SearchRng7 As Range, _
Optional SearchStr8 As Variant, Optional SearchRng8 As Range, _
Optional SearchStr9 As Variant, Optional SearchRng9 As Range, _
Optional SearchStr10 As Variant, Optional SearchRng10 As Range) As Variant
Dim OptArg1 As Variant
Dim OptArg2 As Variant
Dim OptArg3 As Variant
Dim OptArg4 As Variant
Dim OptArg5 As Variant
Dim OptArg6 As Variant
Dim OptArg7 As Variant
Dim OptArg8 As Variant
Dim OptArg9 As Variant
Dim OptArg10 As Variant
Dim OptDelim1 As Variant
Dim OptDelim2 As Variant
Dim OptDelim3 As Variant
Dim OptDelim4 As Variant
Dim OptDelim5 As Variant
Dim OptDelim6 As Variant
Dim OptDelim7 As Variant
Dim OptDelim8 As Variant
Dim OptDelim9 As Variant
Dim OptDelim10 As Variant
Dim Rng1 As String
Dim Rng2 As String
Dim Rng3 As String
Dim Rng4 As String
Dim Rng5 As String
Dim Rng6 As String
Dim Rng7 As String
Dim Rng8 As String
Dim Rng9 As String
Dim Rng10 As String
Dim RtrRng As String
Rng1 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng2 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng3 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng4 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng5 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng6 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng7 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng8 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng9 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng10 = SearchRng1.Address(ReferenceStyle:=xlA1)
RtrRng = ReturnRng.Address(ReferenceStyle:=xlA1)
If IsMissing(SearchStr1) = True Then
OptArg1 = ""
OptDelim1 = ""
ElseIf IsMissing(SearchStr1) = False Then
OptArg1 = "(" & Rng1 & "=""" & SearchStr1 & """)"
OptDelim1 = "*"
End If
If IsMissing(SearchStr2) = True Then
OptArg2 = ""
OptDelim2 = ""
ElseIf IsMissing(SearchStr2) = False Then
OptArg2 = "(" & Rng2 & "=" & SearchStr2 & ")"
OptDelim2 = "*"
End If
If IsMissing(SearchStr3) = True Then
OptArg3 = ""
OptDelim3 = ""
ElseIf IsMissing(SearchStr3) = False Then
OptArg3 = "(" & Rng3 & "=" & SearchStr3 & ")"
OptDelim3 = "*"
End If
If IsMissing(SearchStr4) = True Then
OptArg4 = ""
OptDelim4 = ""
ElseIf IsMissing(SearchStr4) = False Then
OptArg4 = "(" & Rng4 & "=" & SearchStr4 & ")"
OptDelim4 = "*"
End If
If IsMissing(SearchStr5) = True Then
OptArg5 = ""
OptDelim5 = ""
ElseIf IsMissing(SearchStr5) = False Then
OptArg5 = "(" & Rng5 & "=" & SearchStr5 & ")"
OptDelim5 = "*"
End If
If IsMissing(SearchStr6) = True Then
OptArg6 = ""
OptDelim6 = ""
ElseIf IsMissing(SearchStr6) = False Then
OptArg6 = "(" & Rng6 & "=" & SearchStr6 & ")"
OptDelim6 = "*"
End If
If IsMissing(SearchStr7) = True Then
OptArg7 = ""
OptDelim7 = ""
ElseIf IsMissing(SearchStr7) = False Then
OptArg7 = "(" & Rng7 & "=" & SearchStr7 & ")"
OptDelim7 = "*"
End If
If IsMissing(SearchStr8) = True Then
OptArg8 = ""
OptDelim8 = ""
ElseIf IsMissing(SearchStr8) = False Then
OptArg8 = "(" & Rng8 & "=" & SearchStr8 & ")"
OptDelim8 = "*"
End If
If IsMissing(SearchStr9) = True Then
OptArg9 = ""
OptDelim9 = ""
ElseIf IsMissing(SearchStr9) = False Then
OptArg9 = "(" & Rng9 & "=" & SearchStr9 & ")"
OptDelim9 = "*"
End If
If IsMissing(SearchStr10) = True Then
OptArg10 = ""
OptDelim10 = ""
ElseIf IsMissing(SearchStr10) = False Then
OptArg10 = "(" & Rng10 & "=" & SearchStr10 & ")"
OptDelim10 = "*"
End If
MsgBox Evaluate("=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))")
CountUniqueMultiple = "=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))"
End Function
Display More
Any thoughts???
Thanks