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 the <> 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:
