[Excel VBA] Fast Counting of Partial Matches Using COUNTIF and Wildcards

When analyzing survey results, you often need to count the number of cells that contain a specific text (partial match), such as “How many answers include the word ‘Tokyo’?”.

While you can achieve this using a For loop and the Like operator in VBA, it can be extremely slow if you have a large amount of data.

A faster and simpler solution is to use the worksheet function COUNTIF from VBA, combined with wildcards (*). In this article, I will compare the two methods and explain the efficient approach using WorksheetFunction.CountIf.

Method 1: Checking Cells One by One with a Loop (Not Recommended)

First, here is the code using basic VBA commands. It uses the Like operator and the asterisk (*) wildcard to check for partial matches.

Sub CountPartialMatchWithLoop()
    Dim searchArea As Range
    Dim cell As Range
    Dim matchCount As Long
    
    Set searchArea = ThisWorkbook.Worksheets("Data").Range("A1:E5000")
    matchCount = 0
    
    ' Check 5000 cells one by one
    For Each cell In searchArea
        ' Check if the value contains "Tokyo" using the Like operator
        If cell.Value Like "*Tokyo*" Then
            matchCount = matchCount + 1
        End If
    Next cell
    
    ThisWorkbook.Worksheets("Summary").Range("B1").Value = matchCount
    MsgBox "Loop processing complete."
End Sub

While the logic of this code is easy to understand, checking thousands of cells one by one takes a significant amount of time. This is a major disadvantage.

Method 2: Using WorksheetFunction.CountIf (Highly Recommended)

Next, here is the method using WorksheetFunction.CountIf, which utilizes Excel’s high-speed calculation engine.

Code and Explanation

Sub CountPartialMatchWithCountIf()

    ' Declare variables
    Dim searchArea As Range
    Dim searchTerm As String
    Dim criteriaString As String
    Dim matchCount As Long
    
    '--- Settings ---
    Set searchArea = ThisWorkbook.Worksheets("Data").Range("A1:E5000") ' Search range
    searchTerm = "Tokyo" ' String to search for
    '--- End Settings ---

    ' 1. Create search criteria string with wildcards "*"
    criteriaString = "*" & searchTerm & "*"
    
    ' 2. Count all at once using WorksheetFunction.CountIf
    matchCount = WorksheetFunction.CountIf(searchArea, criteriaString)
    
    ' 3. Output result to cell
    ThisWorkbook.Worksheets("Summary").Range("B1").Value = matchCount
    
    MsgBox "Counted cells containing '" & searchTerm & "': " & matchCount
End Sub

This code finishes almost instantly, regardless of the amount of data.

Key Points of the Code

Search Criteria with Wildcards

criteriaString = "*" & searchTerm & "*"

This line is the key to performing a partial match search with COUNTIF. You concatenate asterisks (*) before and after the string you want to search for (searchTerm) using the & operator.

The asterisk * represents “any string of 0 or more characters.” Therefore, the search condition *Tokyo* means “contain ‘Tokyo’ anywhere in the text,” matching values like “West Tokyo“, “Tokyo Head Office”, and “Tokyo Metropolitan Area”.

WorksheetFunction.CountIf

matchCount = WorksheetFunction.CountIf(searchArea, criteriaString)

This calls the standard COUNTIF function from VBA.

• 1st Argument (searchArea): The range of cells to search.
• 2nd Argument (criteriaString): The search condition string including wildcards.

Summary

Method	Pros	Cons
Loop Processing	Logic is intuitive.	Very slow with large amounts of data.
Worksheet Function	Overwhelmingly fast. Concise code.	None.

Just like with SUMIF, when you need to count the number of cells that match a condition, you should use WorksheetFunction.CountIf without hesitation.

Utilizing Excel’s powerful native functions from VBA is a fundamental skill for achieving both high speed and clean code. It is very important for effective VBA programming.