How to Use a SUMPRODUCT IF Formula in Google Sheets

The SUMPRODUCT IF formula in Google Sheets is a powerful function that allows you to multiply and sum the elements of two or more arrays or ranges based on specific criteria. This formula can be especially useful when you need to apply conditional logic to your calculations.

Here's a step-by-step guide on how to use the SUMPRODUCT IF formula in Google Sheets:

1. Open your Google Sheet, or create a new one.
2. Prepare your data in columns or rows, ensuring that the data is organized in a way that makes it easy to apply the SUMPRODUCT IF formula.
3. Click on an empty cell where you want to display the result of the SUMPRODUCT IF formula.
4. Type the following formula in the selected cell:

=SUMPRODUCT(--(criteria_range=criteria), range1, range2)

Where:

• criteria_range is the range that contains the criteria you want to apply.
• criteria is the specific condition you want to use.
• range1 and range2 are the ranges you want to multiply and sum.

5. Replace criteria_range, criteria, range1, and range2 with the appropriate values or cell references based on your data.
6. Press Enter to apply the formula and display the result in the selected cell.

Example

Let's say you have the following data in your Google Sheet:

  A        B        C
1 Product  Quantity Price
2 A        5        10
3 B        7        20
4 A        8        15
5 C        3        25
6 B        2        30

You want to calculate the total cost for all products of type "A". Follow these steps:

1. Click on an empty cell, for example, D1.
2. Type the following formula in D1:

=SUMPRODUCT(--(A2:A6="A"), B2:B6, C2:C6)

3. Press Enter to apply the formula.

The result in cell D1 should display the total cost for all products of type "A", which is 190 in this example (5 * 10 + 8 * 15 = 190).