Table of Contents
SUMPRODUCT in VBA can be used to quickly calculate the sum of the products of two or more ranges or arrays. It is often used to calculate complex formulas with multiple criteria when SUMIF or SUMIFS functions are not suitable. SUMPRODUCT can also save time when working with multiple criteria in large datasets as the formula can be written in one line instead of multiple formulas.
You can use the following basic syntax to use SUMPRODUCT in VBA:
Sub SumProduct()
Range("E2") = WorksheetFunction.SumProduct(Range("B2:B11"), Range("C2:C11"))
End Sub
This particular example will calculate the sum of the product of the values in ranges B2:B11 and C2:C11 and return the result in cell E2.
The following example shows how to use this syntax in practice.
Example: How to Use SUMPRODUCT in VBA
Suppose we have the following dataset that contains information about the sales of various fruit at some grocery store:
Suppose we would like to calculate the total revenue for the store by multiplying the price by the number of units and then taking the sum.
We can use the VBA SumProduct method to do so:
Sub SumProduct()
Range("E2") = WorksheetFunction.SumProduct(Range("B2:B11"), Range("C2:C11"))
End Sub
When we run this macro, we receive the following output:
The sum of the product of the values between ranges B2:B11 and C2:C11 is 139.
This value is shown in cell E2, just as we specified in the macro.
We can verify this is correct by manually calculating the sum of the products between the two ranges:
Sum of Products: (4*1) + (3*5) + (3*4) + (2*7) + (2*3) + (5*5) + (3*5) + (2*6) + (5*6) + (2*3) = 139.
This matches the value calculated by the VBA SumProduct method.