How to Use SUMPRODUCT in VBA (With Example)

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.

Note: You can find the complete documentation for the VBA SumProduct method here.

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