Arrow left
Back to product guides
Google Sheets Formulas

How to Use SUMPRODUCT Formula in Google Sheets

This article teaches you how to utilize the SUMPRODUCT formula in Google Sheets.

How to use SUMPRODUCT function in Google Sheets

  1. Type “=SUMPRODUCT” or go to the “Insert” tab  ➝ “Function” ➝ “Array” ➝ “SUMPRODUCT”.
  2. Input ranges by selecting fields** or type in ranges** between the brackets.
  3. 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.

How to insert SUMPRODUCT function from the menu bar in Google Sheets

The following is a generic formula of the SUMPRODUCT function.

=SUMPRODUCT(array1, [array2, …])

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.

The examples of how to use SUMPRODUCT function in Google Sheets

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.  

The examples of how to utilize SUMPRODUCT function with criteria

Before looking at the other two examples, understand what the SUMPRODUCT formula with criteria does by digging into the example (iii). 

What SUMPRODUCT formula with criteria conceptually does

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.

What are the other formulas related to mathematics and statistics

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.

How to Do Math in Google Sheets for Beginners

ADD Function in Google Sheets: Explained

MINUS Function in Google Sheets: Explained

DIVIDE Function in Google Sheets: Explained

MULTIPLY Function in Google Sheets: Explained

PRODUCT Function in Google Sheets: Explained

How to Use SUM Function in Google Sheets

How to Use MAX Function in Google Sheets

How to Use MIN Function in Google Sheets

How to Use MEDIAN Function in Google Sheets

How to Use AVERAGE Formula in Google Sheets

How to Use MODE Function in Google Sheets

MOD Function in Google Sheets: Explained

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

Analyze your live financial data in a snap

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.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711