Microsoft Office Forums - View Single Post - Matching Criteria against a range when criteria is not in contiguous cells??

macropod · 08-15-2014, 12:58 AM

Although a rather more complex could be made to do the job, this might be better handled via a UDF:

Code:

Function UniqueCount(Src1 As Range, Optional Src2 As Range, Optional Src3 As Range, _
  Optional Src4 As Range, Optional Src5 As Range, Optional Src6 As Range) As Long
Application.Volatile
Dim i As Long, StrSrc As String, StrTmp As String
StrSrc = Src1
If Not Src2 Is Nothing Then StrSrc = StrSrc & "," & Src2
If Not Src3 Is Nothing Then StrSrc = StrSrc & "," & Src3
If Not Src4 Is Nothing Then StrSrc = StrSrc & "," & Src4
If Not Src5 Is Nothing Then StrSrc = StrSrc & "," & Src5
If Not Src6 Is Nothing Then StrSrc = StrSrc & "," & Src6
StrTmp = "|"
  For i = 0 To UBound(Split(StrSrc, ","))
    If InStr(StrTmp, "|" & Split(StrSrc, ",")(i) & "|") = 0 Then
      If Split(StrSrc, ",")(i) <> "" And Split(StrSrc, ",")(i) <> "Nobody on Duty" Then
      StrTmp = StrTmp & Split(StrSrc, ",")(i) & "|"
      End If
    End If
  Next
UniqueCount = UBound(Split(StrTmp, "|")) - 1
End Function

To use it, simply add the code to a normal code module in your workbook and use a formula like:
=UniqueCount(E2,G2,I2,K2)
As code, the function can handle from 1 to 6 input ranges, giving you a bit more flexibility in case you need it.