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” and B2: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.
This represents the sum of the products between the values in the Price and Units columns only for the rows where Store is “A” and Item is “Apples.”
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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use SUMPRODUCT Across Multiple Sheets
Excel: Using SUMPRODUCT Only with Values Greater Than Zero
Excel: How to Use SUBTOTAL with SUMPRODUCT
