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.
If you don’t get the solution you are looking for in this article, or you have further questions related to mathematics or statistics, you may find the answers in the following articles.
Go to the following articles to learn basic formulas in Google Sheets.
Navigate to the pages below to learn how to sum, count, or average numbers with a condition or multiple conditions.
How to Use SUMIF Function in Google Sheets - sum up the numbers that meet a criterion
SUMIFS Function in Google Sheets: Explained - sum up the numbers that meet multiple criteria
How to Use COUNTIF Function in Google Sheets - count the number of cells that meet a requirement
How to Use COUNTIFS function in Google Sheets - count the number of cells that meet multiple requirements
AVERAGEIF Function in Google Sheets: Explained - average the figures that meet a standard
AVERAGEIFS Function in Google Sheets: Explained - average the figures that meet multiple standards
AVERAGE.WEIGHTED Function in Google Sheets: Explained - use this formula to calculate the weighted average
MAXIFS Function in Google Sheets: Explained - seek for the maximum value that meets specific criteria
MINIFS Function in Google Sheets: Explained - seek for the minimum value that meets specific criteria
Visit the following post if you are interested in learning how to count the number of specific cells.
COUNT Function in Google Sheets: Explained - count the number of cells containing numeric values (except for blank ones)
COUNTA Function in Google Sheets: Explained - count the number of cells incorporating all types of values containing text and date, except for blank ones
COUNTBLANK Function in Google Sheets: Explained - count the number of blank cells
COUNTUNIQUE Function in Google Sheets: Explained - count the number of unique cells
You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇