LiveFlow raises $13.5 million Series A led by New York-based Valar Ventures and launches LiveFlow Next to revolutionize accounting!
Read More
Arrow
Back to 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.

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. 

Save hours formatting your next financial spreadsheet!
100+ templates, all free to use.
Free Spreadsheet Templates

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.

How to use the RIGHT function
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.

Insert the SCAN function
The examples of how to use SUMPRODUCT function in Google Sheets

Learn Sheets for financial analysis today!
LiveFlow Academy teaches you the basics for free. Certificates available!
Enroll in LiveFlow Academy

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.  

Use the SCAN function
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). 

SUMPRODUCT function from the menu bar
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.

Turn Quickbooks Online data into a Google Sheets dashboard
100+ templates, update automatically, totally secure!
Book a Demo

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

Automate financial reporting with LiveFlow

Cta Photo

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how!

Get personal help

We guarantee you personal help on chat or Zoom within maximum 6 hours between 9am and 10pm EST.
Blue Tick
Email us at: help@liveflow.io

Liked this article? Then you'll love the ones below

Supercharge your financial reporting today

See LiveFlow in action and discover how to streamline your workflows.