Table of Contents
The SUMIF and SUMIFS Functions in VBA are used to quickly calculate the sum of all cells that meet certain criteria. The SUMIF function calculates the sum of cells based on one criteria, while the SUMIFS function calculates the sum of cells based on multiple criteria. Both functions take 3 arguments: the range of cells to be summed, the criteria for inclusion, and the range of cells containing the criteria. For more complex calculations, you can use the SUMPRODUCT function.
You can use the following methods to write SUMIF and SUMIFS functions using VBA in Excel:
Method 1: SUMIF Function in VBA
Sub Sumif_Function()
Range("E2") = WorksheetFunction.Sumif(Range("A2:A12"), "Mavs", Range("B2:B12"))
End Sub
This particular example will sum the values in the range B2:B12 only where the corresponding value in the range A2:A12 is equal to “Mavs” and assign the result to cell E2.
Method 2: SUMIFS Function in VBA
Sub Sumifs_Function()
Range("E2") = WorksheetFunction.SumIfs(Range("C2:C12"), Range("A2:A12"), "Mavs", Range("B2:B12"), ">20")
End Sub
This particular example will sum the values in the range C2:C12 only 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: SUMIF Function in VBA
Suppose we would like to calculate the sum of values in the points column only for the players who are on the Mavs team.
We can create the following macro to perform this SUMIF function:
Sub Sumif_Function()
Range("E2") = WorksheetFunction.Sumif(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 73.
This represents the sum of values in the points column for player on the Mavs team.
Sum of Points for Mavs players: 22 + 10 + 29 + 12 = 73.
Example 2: SUMIFS Function in VBA
Suppose we would like to calculate the sum of values in the assists column only for the players who 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 SUMIFS function:
Sub Sumifs_Function()
Range("E2") = WorksheetFunction.SumIfs(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 18.
This represents the sum of values in the points column for players on the Mavs team who scored more than 20 points.
Note: In this example, we created a SUMIFS function using two criteria ranges but you can use as many criteria ranges as you’d like within the WorksheetFunction.SumIfs method.