Table of Contents
The SUMPRODUCT function in Excel allows users to multiply corresponding values in multiple columns and then sum the results. This function can be used to efficiently calculate the sum of multiple columns in a spreadsheet, by taking the product of each value in a row and then summing those products. This can be particularly useful for financial or numerical data, where the sum of different factors needs to be calculated. By using the SUMPRODUCT function, users can quickly and accurately calculate the total sum of multiple columns, saving time and effort compared to manual calculations.
Excel: Use SUMPRODUCT with Multiple Columns
The SUMPRODUCT function in Excel returns the sum of the products of two corresponding arrays.
Here are two ways to use the SUMPRODUCT function with multiple columns in Excel:
Method 1: Use SUMPRODUCT with Multiple Columns with AND Condition
=SUMPRODUCT((A2:A11="A")*(B2:B11="Apples"), C2:C11, D2:D11)
This particular formula will calculate the SUMPRODUCT of values in the range C2:C11 and D2:D11 only for the rows where A2:A11 is equal to “A” andB2:B11 is equal to “Apples.”
Method 2: Use SUMPRODUCT with Multiple Columns with OR Condition
=SUMPRODUCT((A2:A11="A")+(B2:B11="Apples"), C2:C11, D2:D11)
This particular formula will calculate the SUMPRODUCT of values in the range C2:C11 and D2:D11 only for the rows where A2:A11 is equal to “A” or B2:B11 is equal to “Apples.”
The following examples show how to use each method in practice with the following dataset in Excel:
Example 1: Use SUMPRODUCT with Multiple Columns with AND Condition
Suppose we would like to calculate the sum of the products between the Price and Units columns only for the rows where the store is equal to “A” and the item is equal to “Apples.”
We can use the following formula to do so:
=SUMPRODUCT((A2:A11="A")*(B2:B11="Apples"), C2:C11, D2:D11)
We’ll type this formula into cell F2 and press Enter:
The formula returns a value of 25.
Example 2: Use SUMPRODUCT with Multiple Columns with OR Condition
Suppose we would like to calculate the sum of the products between the Price and Units columns only for the rows where the store is equal to “A” or the item is equal to “Apples.”
We can use the following formula to do so:
=SUMPRODUCT((A2:A11="A")+(B2:B11="Apples"), C2:C11, D2:D11)
We’ll type this formula into cell F2 and press Enter:
The formula returns a value of 121.
This represents the sum of the products between the values in the Price and Units columns only for the rows where Store is “A” or Item is “Apples.”
Note: You can find the complete documentation for the SUMPRODUCT function .
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the SUMPRODUCT function in Excel to calculate the sum of multiple columns?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-multiple-columns/
stats writer. "How can I use the SUMPRODUCT function in Excel to calculate the sum of multiple columns?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-multiple-columns/.
stats writer. "How can I use the SUMPRODUCT function in Excel to calculate the sum of multiple columns?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-multiple-columns/.
stats writer (2024) 'How can I use the SUMPRODUCT function in Excel to calculate the sum of multiple columns?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sumproduct-function-in-excel-to-calculate-the-sum-of-multiple-columns/.
[1] stats writer, "How can I use the SUMPRODUCT function in Excel to calculate the sum of multiple columns?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SUMPRODUCT function in Excel to calculate the sum of multiple columns?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.