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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Rows in Range

VBA: How to Write SUMIF and SUMIFS Functions

VBA: How to Write AVERAGEIF and AVERAGEIFS Functions