You can use the following methods to create a SUMPRODUCT IF formula in Google Sheets:
Method 1: SUMPRODUCT IF with One Criteria
=SUMPRODUCT(--(A2:A12="value"), C2:C12, D2:D12)
This formula finds the sum of the products between columns C and D only where the value in column A is equal to “value.”
Method 2: SUMPRODUCT IF with Multiple Criteria
=SUMPRODUCT(--(A2:A12="value1"),--(B2:B12="value2"), C2:C12, D2:D12)
This formula finds the sum of the products between columns C and D only where the value in column A is equal to “value1” and the value in column B is equal to “value2.”
The following examples show how to use each method with the following dataset in in Google Sheets:
Example 1: SUMPRODUCT IF with One Criteria
We can use the following formula to calculate the sum of the products between the Quantity and Price columns only for the rows where the Store column is equal to “A”:
=SUMPRODUCT(--(A2:A12="A"), C2:C12, D2:D12)
The following screenshot shows how to use this syntax in practice:
The sum of the products between Quantity and Price for Store A turns out to be 86.
We can manually verify this is correct by taking the sum of the products between quantity and price for store A only:
SUMPRODUCT for Store A: 1*6 + 3*12 + 4*6 + 4*4 + 1*4 = 86.
Example 2: SUMPRODUCT IF with Multiple Criteria
We can use the following formula to calculate the sum of the products between the Quantity and Price columns only for the rows where the Store column is equal to “A” and the Product column is equal to “Gloves”:
=SUMPRODUCT(--(A2:A12="A"),--(B2:B12="Gloves"), C2:C12, D2:D12)
The following screenshot shows how to use this syntax in practice:
The sum of the products between Quantity and Price for “Gloves” in store A turns out to be 30.
We can manually verify this is correct by taking the sum of the products between quantity and price for “Gloves” in store A only:
SUMPRODUCT for “Gloves” in Store A: 1*6 + 4*6 = 30.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Multiply Column by a Constant
Google Sheets: How to Multiply Column by a Percentage
Google Sheets: How to Rank Items by Multiple Columns
