Table of Contents
The SUMPRODUCT function can be used with multiple columns by multiplying the values within each column together and then summing the results. This can be especially useful when working with large data sets that contain multiple columns of numbers. For example, if you wanted to sum the total of two columns, you could use the SUMPRODUCT function to multiply each value in column A by each value in column B, and then sum the results. This can help save time as it eliminates the need to manually create formulas for each individual cell.
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.
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 .