You can use the following methods to write AVERAGEIF and AVERAGEIFS functions using VBA in Excel:

**Method 1: AVERAGEIF Function in VBA**

**Sub Averageif_Function()
Range("E2") = WorksheetFunction.AverageIf(Range("A2:A12"), "Mavs", Range("B2:B12"))
End Sub**

This particular example will calculate the average value in the range **B2:B12** where the corresponding value in the range **A2:A12** is equal to “Mavs” and then assign the result to cell **E2**.

**Method 2: AVERAGEIFS Function in VBA**

**Sub Averageifs_Function()
Range("E2") = WorksheetFunction.AverageIfs(Range("C2:C12"), Range("A2:A12"), "Mavs", Range("B2:B12"), ">20")
End Sub**

This particular example will calculate the average value in the range C2:C12 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: AVERAGEIF Function in VBA**

Suppose we would like to calculate the average value in the points column for players who are on the Mavs team.

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

**Sub Averageif_Function()
Range("E2") = WorksheetFunction.AverageIf(Range("A2:A12"), "Mavs", Range("B2:B12"))
End Sub**

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

Notice that cell **E2** contains a value of **18.25**.

This tells us that the average value in the points column for the players on the Mavs team is 18.25.

We can verify that this is correct by manually calculating the average points value for the players on the Mavs:

Average points for Mavs players: (22 + 10 + 29 + 12) / 4 = **18.25**.

**Example 2: AVERAGEIFS Function in VBA**

Suppose we would like to calculate the average value in the assists column for the players 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 AVERAGEIFS function:

**Sub Averageifs_Function()
Range("E2") = WorksheetFunction.AverageIfs(Range("C2:C12"), 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 **9**.

This tells us that the average assists among players who are on the Mavs team *and* scored more than 20 points is 9.

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

**Additional Resources**

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

VBA: How to Calculate Average Value of Range

VBA: How to Write SUMIF and SUMIFS Functions

VBA: How to Write COUNTIF and COUNTIFS Functions