How to Use SUMPRODUCT Formula in Google Sheets
This article teaches you how to utilize the SUMPRODUCT formula in Google Sheets.
What is the SUMPRODUCT function in Google Sheets?
The SUMPRODUCT function in Google Sheets multiplies corresponding values in two or more arrays and returns the sum of those products.
When do you use the SUMPRODUCT formula in Google Sheets?
Here are some common situations where you might use the SUMPRODUCT formula:
- Computing the total sum of products: The SUMPRODUCT function allows you to compute the aggregated amount of products. For example, when you have a sales dataset containing the average unit price and the number of units by salesperson, you can easily calculate the total sales value without calculating the sales from each salesperson, using the SUMPRODUCT formula.
- Counting items that meet multiple criteria: Similar to the above example, but with a column containing 0 or 1, (0 for items that don’t meet criteria and 1 for those that do), you can calculate the sum of products that meet specific requirements.
How to use SUMPRODUCT function in Google Sheets
- Type “=SUMPRODUCT(” or go to the “Insert” tab ➝ “Function” ➝ “Array” ➝ “SUMPRODUCT”.
- Input ranges by selecting fields** or type in ranges** between the brackets.
- Press the Enter key on your keyboard.
*You don’t need to add quotation marks when you enter the formula name.
**The number of cells in the selected ranges should be equal.
The following is a generic formula of the SUMPRODUCT function.
Array 1: This is a range you want to include in a multiplication done by the SUMPRODUCT function.
You need to keep entering ranges until you incorporate all groups of numbers you want to include into the multiplication.
Let’s see examples. Imagine a small organization of 10 people and that you need to get the total sales amount and total costs from the data table shown in the following screenshot.
You need to keep it in mind to select the proper ranges so that an item in the same order in each sheet corresponds to each other.
(i) Total sales (calculates as # of Unit multiplied by Price per Unit)
The first example (“Total sales”) is relatively simple and contains two columns in a range.
(ii) Total costs (calculared as # of Unit multiplied by Price per Unit multiplied by Cost (%)
As you can see, the second example includes a different range (“Cost (% of Sales)”) in the formula”). You can add a factor in multiplication by inserting a field you want to have.
How do I SUMPRODUCT with two criteria?
To run SUMPRODUCT formula with two criteria, you need to incorporate the two criteria in the formula. See how to do it with examples.
(iii) Total sales from Group W, Division A
Assume you want to understand the total sales and costs from the sales department in Group W in Division A, without calculating sales or costs from each person. In the formula, brackets should enclose each of the two conditions (Division A and Group W) as in the picture below.
The part of conditions can be texts, dates, values, or a combination of them.
Before looking at the other two examples, understand what the SUMPRODUCT formula with criteria does by digging into the example (iii).
For simplification, assume that the formula gives 1 or 0 to each item in selected ranges for criteria. If an item matches a standard, it gets 1 and if not, it is given 0. So, in case of the example (iii), for the “Division column”, 1 is given to “A”s, and 0 is assigned to “B”s.
The same operation happens in the Group column as well. “W”s are provided 1, but the other letters get 0. These numbers (1 or 0) become factors in multiplication for each item. If we focus on “Person 1,” the calculation result for the person is 10 because he is in Group W in Division A (1 x 1 x 1 x 10=10).
However, in the case of “Person 2”, the result is 0 because the person doesn’t meet either criterion (0 x 0 x 2 x 10=0). The SUMPRODUCT formula with a criterion or more automatically runs this sort of calculation. In the example, the formula sums up the values of items that meet the conditions - Person 1, Person 5, and Person 6 and you get 120.
Get back to the other examples.
(iv) Total costs from Group W, Division A, after 4/30/2022
In this formula, a standard about Date is added to the formula in the example (iii).
(v) Total sales from Group W, Division A, after 4/30/2022, with # of Unit greater than or equal to 6
In this formula, an additional criterion about “# of Unit” is incorporated in addition to the criteria in the example (iv).
How do I calculate SUMPRODUCT across multiple sheets?
You can use SUMPRODUCT for multiple items across sheets by just selecting a range on different sheets. For instance, If you are on Sheet A and want to refer to data on Sheet B and C, you just need to go to Sheet B and Sheet C when you select ranges in the SUMPRODUCT formula.
Again, the number of cells in each field should be equal and the order of items should be consistent across all ranges.
Therefore, consider gathering and streamlining all relevant information in a sheet (Sheet A in this example) by pulling out data from Sheet B and Sheet C (through cell references or copy-and-paste).
It helps you to avoid a wrong reference of a range, an incorrect selection of data, or a lack of clear visibility. Ideally, you have data on the same sheet as much as possible.
