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:

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:

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

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:

Output:

Using not equal to operator in CountIF

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

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:

Result:

Specifying last letters to search and get count with wildcard *

We will search for products ending with “Set”:

Result: