This article teaches you how to utilize the SUMPRODUCT formula in Google Sheets.
*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.
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).
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.
Are you learning this formula to process financial data, build up a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time.
Book a demo to learn more about LiveFlow.
Learn how to do this step-by-step in the video below 👇