VBA Countif with OR criteria

Question

I'm trying to use countif within VBA with multiple OR criteria and I found the old post below:

However when I try to use it in my project it returns "Compilation error: invalid character."

This is the line I'm trying to compile:

pExcludedItems = Sum(CountIf(.Range(.Cells(2, colKeep), .Cells(pNumStdData, colKeep)), {"E", "E_*"}))

Also tried:

pExcludedItems = Sum(CountIf(.Range(.Cells(2, colKeep), .Cells(pNumStdData, colKeep)), {""E"", ""E_*""}))

Can't find a solution elsewhere. Thank you very much.

user_9814069 · Answer

Sum-up Countif Results in VBA

You can use the late-bound:

pExcludedItems = Application.Sum(Application.CountIf(.Range(.Cells(2, colKeep), .Cells(pNumStdData, colKeep)), [{"E", "E_*"}]))

Also, Instead of [{"E", "E_*"}] you could use (the common):
```
Array("E", "E_*")
```
Note that Sum is needed only because you have multiple criteria. If there is only one criterion, you would rather use, e.g. the simple:
```
pExcludedItems = Application.CountIf(rg, "E")
```

A Practical Example

Copy the following to a standard module in a new workbook.
Write some strings, some being just E and some being E_* (e.g. E_One, E_Two etc.), in column C of Sheet1.
Monitor the results printed in the VBE Immediate window Ctrl+G.

Option Explicit

Sub Test()
    
    Const wsName As String = "Sheet1" ' Tab Name
    Const fRow As Long = 2 ' First Row
    Const Col As Long = 3 ' Column 'C'
    ' You cannot use a constant in the following line.
    Dim cCrit As Variant: cCrit = Array("E", "E_*") ' [{"E","E_*"}] '
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    ' Last Row
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    If lRow < fRow Then Exit Sub ' no data in column range
    
    ' Range
    Dim rg As Range: Set rg = ws.Range(ws.Cells(fRow, Col), ws.Cells(lRow, Col))
    
    ' Print to the immediate window (Ctrl+G).
    
    ' Get result.
    Dim pExcludedItems As Long
    pExcludedItems = Application.Sum(Application.CountIf(rg, cCrit))
    Debug.Print "Result:   : " & pExcludedItems

    ' CountIf returns an array of counts (criteria is an array).
    Dim cCount As Variant: cCount = Application.CountIf(rg, cCrit)
    Debug.Print "CountArray: " & Join(cCount, ", ")
    
    ' Sum returns the sum of the elements of the array.
    Dim cSum As Long: cSum = Application.Sum(cCount)
    Debug.Print "Sum       : " & cSum

End Sub

VBA Countif with OR criteria

1 Answers

Sum-up Countif Results in VBA