Table of Contents
A SUMPRODUCT IF formula in Google Sheets is a powerful combination of SUMPRODUCT and IF functions that allows you to apply multiple criteria to a range of data and calculate the sum of only the cells that meet the criteria. This type of formula can be used to quickly summarize data and can be used in combination with other functions like COUNTIF and AVERAGEIF to provide more detailed information.
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.