VBA Countif Multiple Criteria

[helpexcel]

Trying to figure out how to make the "Str" criteria have multiple options if the value in B1 is "All".

Dim rngCriteria1 As Range
Dim rngCriteria2 As Range
Dim lastorw As Long
Dim LastCol As Long
Dim Str As String

With Sheet1
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1,  Columns.Count).End(xlToLeft).Column
End With

With Sheet6
    If .Range("B1") = "All" Then
        Str = "Apple, Pair, Orange"
    Else: Str = .Range("B1")
    End If
End With

With Sheet6
    Set rngCriteria1 = Sheet1.Range("A2:M" & lastrow)
    Set rngCriteria2 = Sheet1.Range("B2:M" & lastrow)

    .Range("B3") = WorksheetFunction.CountIfs(rngCriteria1, "=A3", rngCriteria2, Str)

    Set rngCriteria1 = Nothing
    Set rngCriteria2 = Nothing
End With

 

[jasonb75]

Perahps

With Sheet6
    If .Range("B1") = "All" Then
        Str = "*"
    Else: Str = .Range("B1")
    End If
End With

Or use Str = "?*" if you need to ignore any blanks in the count range.

 

[helpexcel]

Thanks!!

 

[jasonb75]

Something I just noticed that I missed earlier, I'm not sure that "=A3" will work correctly for the first criteria, it might be safer to use Range("A3").Value instead.

 

[helpexcel]

yep, i caught that too. thanks!!