Excel / VBA CountIf Method with 7 Examples

What is CountIf method in VBA/Excel?

The COUNTIF is an Excel function that you may use in VBA as well.
For that, you may use the WorksheetFunction object’s CountIf method.
Just like the Excel function, the CountIf returns the count of the number of cells in the given range that meets the given criteria.

Syntax of using CountIf method in VBA

expression.CountIf (Arg1, Arg2)

Where:

Arg1 = Range
Arg2 = criteria

Let us show you examples of using CountIf with both required arguments with Excel data.

An example of using range and searching text to get the count of cells

For our examples, we will use the following sheet:

In this example, we will get the total count of cells that contain “In Stock” text. For that, we created a range of (B2 to D10).

Then we used the CountIf function as follows:

Sub CountIf_ex()

Dim rng As Range

Dim cnt_res As Integer

Set rng = Range(“B2:D10”)

cnt_res = WorksheetFunction.CountIf(rng, “In Stock”)

MsgBox “Total Products In Stock = “ & cnt_res

End Sub

Result:

An example of using a cell in the criteria

This time we used a cell value as the Arg2 in the CountIf.
We will get the count of the total number of products that are “Out of Stock” and display the result in the message box.
Rather than using text value directly, we used the D7 cell as follows.

Code:

Sub CountIf_ex()

Dim rng As Range
Dim cnt_res As Integer

Set rng = Range("B2:D10")
cnt_res = WorksheetFunction.CountIf(rng, Range("D7"))

'Get total number "out of stock" products
MsgBox "Total Products Out of Stock = " & cnt_res

End Sub

Output:

Using greater than with numeric value

You may also use operators like less than (<), greater than (>), etc. in the CountIf function.

For this example, we will get the count of cells in the Price column.
The message box displays the number of products whose price is more than $5.

Code:

Sub CountIf_ex()

Dim rng As Range
Dim cnt_res As Integer

Set rng = Range("B2:D10")
cnt_res = WorksheetFunction.CountIf(rng, ">5")

'Get total number products with price greater than 5
MsgBox "Number of Products with Price > 5 = " & cnt_res

End Sub

Output:

Using not equal to operator in CountIF

Only return the count of those cells in the C2:C10 range whose price is not equal to $3.5. For that, we will use <> operator as follows:

Sub CountIf_ex()
Dim cnt_res As Integer

cnt_res = WorksheetFunction.CountIf(Range("C2:C10"), "<>3.5")

MsgBox "Count of Products Not Equal to 3.5  = " & cnt_res

End Sub

Result:

**Using * wildcard example**

The wildcards can also be used in VBA CountIf like with the Excel COUNTIF function.

In the example below, we will search for products whose names start with the letter “T”. Have a look:

Sub CountIf_ex()
Dim cnt_res As Integer

cnt_res = WorksheetFunction.CountIf(Range("B2:C10"), "T*")

MsgBox "Number of Products Starting with 'T' = " & cnt_res

End Sub

Result:

Specifying last letters to search and get count with wildcard *

We will search for products ending with “Set”:

Sub CountIf_ex()
Dim cnt_res As Integer

cnt_res = WorksheetFunction.CountIf(Range("B2:C10"), "*Set")

MsgBox "Number of Products ending with 'Set' = " & cnt_res

End Sub

Result: