Table of Contents
The SUMPRODUCT function in VBA is a powerful tool that allows users to multiply corresponding elements in two or more arrays and then sum the results. This function is useful for performing complex calculations and data analysis. To use the SUMPRODUCT function, the user must first define the arrays and their respective ranges. Then, the function can be applied to these arrays to calculate the desired result. For instance, if a company has sales data for different products in different regions, the SUMPRODUCT function can be used to calculate the total sales for a specific product in a particular region. This function saves time and effort by eliminating the need for manual calculations. Overall, the SUMPRODUCT function in VBA is an essential tool for efficiently handling large sets of data and performing advanced calculations.
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 SubWhen 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.
Cite this article
stats writer (2024). How can I use the SUMPRODUCT function in VBA with an example?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-vba-with-an-example/
stats writer. "How can I use the SUMPRODUCT function in VBA with an example?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-vba-with-an-example/.
stats writer. "How can I use the SUMPRODUCT function in VBA with an example?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-vba-with-an-example/.
stats writer (2024) 'How can I use the SUMPRODUCT function in VBA with an example?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-vba-with-an-example/.
[1] stats writer, "How can I use the SUMPRODUCT function in VBA with an example?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SUMPRODUCT function in VBA with an example?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.