You can use the following methods to write COUNTIF and COUNTIFS functions using VBA in Excel:

Method 1: COUNTIF Function in VBA

Sub Countif_Function()
    Range("E2") = WorksheetFunction.Countif(Range("B2:B12"), ">20")
End Sub

This particular example will count the number of values in the range B2:B12 that are greater than 20 and assign the result to cell E2.

Method 2: COUNTIFS Function in VBA

Sub Countifs_Function()
    Range("E2") = WorksheetFunction.CountIfs(Range("A2:A12"), "Mavs", Range("B2:B12"), ">20")
End Sub

This particular example will count the number of rows where the value in the range A2:A12 is equal to “Mavs” and the value in the range B2:B12 is greater than 20 and then assign the result to cell E2.

The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:

Example 1: COUNTIF Function in VBA

Suppose we would like to count the number of values in the points column that are greater than 20.

We can create the following macro to perform this COUNTIF function:

Sub Countif_Function()
    Range("E2") = WorksheetFunction.Countif(Range("B2:B12"), ">20")
End Sub

When we run this macro, we receive the following output:

Notice that cell E2 contains a value of 6.

This tells us that there are 6 values in the points column that are greater than 20.

Example 2: COUNTIFS Function in VBA

Suppose we would like to count the number of rows that meet the following criteria:

• Player is on the Mavs team.
• Player scored more than 20 points.

We can create the following macro to perform this COUNTIFS function:

Sub Countifs_Function()
    Range("E2") = WorksheetFunction.CountIfs(Range("A2:A12"), "Mavs", Range("B2:B12"), ">20")
End Sub

When we run this macro, we receive the following output:

Notice that cell E2 contains a value of 2.

This tells us that there are two rows where the player is on the Mavs team and the player scored more than 20 points.

Note: In this example, we created a COUNTIFS function using two criteria ranges but you can use as many criteria ranges as you’d like within the WorksheetFunction.CountIfs method.

VBA: How to Count Number of Rows in Range