How to Use SUMPRODUCT Function in Google Sheets

Published on

Use the SUMPRODUCT function when you want to obtain the sum of products without first calculating the individual products and then summing them up. This approach is both a time and space saver.

You can specify two or more equal-sized arrays within this function, creating new possibilities. For instance, you can employ this function for conditional summing or product calculations. How?

You can use logical tests in a column to produce TRUE or FALSE (1 or 0). If a logical test returns 0, the product or the resulting value in that specific row will be 0.

Therefore, you can apply the SUMPRODUCT function in Google Sheets in scenarios similar to those where you would use SUMIF or SUMIFS.

Syntax and Arguments

Syntax:

SUMPRODUCT(array1, [array2, ...])

Arguments:

  • array1: The first array whose values will be multiplied with corresponding values in the second array.
  • array2: The second array whose values will be multiplied with corresponding values in the first array.

The second argument is optional. Therefore, if you only specify array1, the formula will simply return the sum.

Before diving into examples, you can make a copy of my sample sheet, which contains all the sample datasets and formulas we will detail in this tutorial.

Sample Sheet

SUMPRODUCT Example (Basic Use): Utilizing Two Arrays

Consider a scenario with two columns: quantities (array1) and prices (array2). To calculate the total cost, you can use the SUMPRODUCT function:

The sample data is in cell range A1:C5, where column A contains product names, B contains quantity, and C contains price. A1:C1 is designated for the field labels (header).

To obtain the total cost, you can use the following SUMPRODUCT formula:

=SUMPRODUCT(B2:B5, C2:C5)
SUMPRODUCT Basic Example: Two Arrays

This is equivalent to:

=B2*C2 + B3*C3 + B4*C4 + B5*C5

LET Use Case:

=LET(
   quantity, B2:B5, 
   price, C2:C5, 
   SUMPRODUCT(quantity, price)
)

The LET use case proves beneficial when handling complex SUMPRODUCT formulas in Google Sheets. In this example, we utilized this function to assign meaningful names to the value expressions B2:B5 (named “quantity”) and C2:C5 (named “price”). These meaningful names were then used in the subsequent calculation, enhancing the clarity and readability of the formula.

SUMPRODUCT Example (Basic Use): Utilizing Three Arrays

Let’s consider a new scenario where we have a product column, quantity column, price column, and a discount percentage column.

This time we have the data in A1:D5, where A1:D1 is dedicated to the field labels.

The goal is to calculate the total cost after accounting for the discounts using the SUMPRODUCT function in Google Sheets.

Formula:

=SUMPRODUCT(B2:B5, C2:C5, 1-(D2:D5))
SUMPRODUCT Basic Example: Three Arrays

Which is equal to:

=(5 * 10 * (1-10%)) + (3 * 15 * (1-20%)) + (2 * 20 * (1-5%)) + (4 * 8 * (1-15%))

If you are wondering why deduct 1 from the discount percentage, i.e., 1 – D2:D5, it’s to create weightings that represent the complement of the discount percentages.

For example, if the discount is 10%, deducting it from 1 results in 90%, representing the portion of the original value retained after applying the discount.

LET Use Case:

=LET(
   quantity, B2:B5, 
   price, C2:C5, 
   discount, D2:D5, 
   SUMPRODUCT(quantity, price, 1-discount)
)

This LET function use case assigns meaningful names to the value expressions, enhancing the clarity and readability of the SUMPRODUCT formula.

Note: SUMPRODUCT is an array function in Google Sheets. Therefore, you do not need to explicitly specify the ArrayFormula function when using non-array value expressions, such as 1 – D2:D5, within this function.

Complex Use of the SUMPRODUCT Function in Google Sheets

Let’s revisit the above table with an added column, E2:E5, indicating “Yes” or “No.” If it’s “Yes,” the specified discount should be applied; otherwise, it shouldn’t.

Now, let’s address this complex scenario using the SUMPRODUCT function in Google Sheets:

=SUMPRODUCT(B2:B5, C2:C5, IF(E2:E5="Yes", (1 - D2:D5), 1))
SUMPRODUCT Function Complex Use Case Examples in Google Sheets

Where:

  • array1: B2:B5
  • array2: C2:C5
  • array3: IF(E2:E5=”Yes”, (1 – D2:D5), 1)

Array3 introduces complexity to the SUMPRODUCT formula, incorporating an IF logical test. The formula assigns a weightage of 1 if the discount is not to be applied; otherwise, it uses (1 – D2:D5), representing the complement of the discount percentages.

LET Use Case:

=LET(
   quantity, B2:B5, 
   price, C2:C5, 
   discount, D2:D5, 
   condition, E2:E5, 
   SUMPRODUCT(quantity, price, IF(condition="Yes", 1-discount, 1))
)

SUMPRODUCT Function for Conditional Sum in Google Sheets

As mentioned earlier, specifying only array1 in the SUMPRODUCT formula sums the column range that the array represents. We’ve also explored a logical use case in the SUMPRODUCT example above.

Now, let’s leverage the SUMPRODUCT feature for multiple criteria conditional sums in Google Sheets.

Consider the following table representing sales targets assigned to different salespersons:

Complex Conditional SUM using SUMPRODUCT in Google Sheets

Let’s see how to use SUMPRODUCT for conditional sums in its fullest diversity in Google Sheets.

We want to calculate the sales target of “Philip” from the areas “East” and “West.”

Formula:

=SUMPRODUCT((A2:A6="Philip")*((B2:B6="East")+(B2:B6="West")), C2:C6)

Where:

  • array1: (A2:A6="Philip")*((B2:B6="East")+(B2:B6="West")).
    • This has two parts:
      • Part 1: (A2:A6="Philip") returns TRUE wherever the name matches.
      • Part 2: (B2:B6="East")+(B2:B6="West") – This addition of two logical tests results in 1 (TRUE) wherever either of the criteria matches, else 0 (FALSE).
    • The product of Part 1 and Part 2 returns 1 for matching all the criteria, else 0. This is the array1 in SUMPRODUCT.
  • array2: C2:C6

LET Use Case:

=LET(
   salesperson, A2:A6, 
   area, B2:B6, 
   target, C2:C6, 
   SUMPRODUCT((salesperson="Philip")*((area="East")+(area="West")), target)
)

Resources

This tutorial covers both the basic and complex uses of the SUMPRODUCT function in Google Sheets. Additionally, we have explored its application in complex conditional sum scenarios. Here are a few more related tutorials.

  1. How to Use Date Difference as Criteria in SUMPRODUCT in Google Sheets
  2. Difference Between SUMIFS and SUMPRODUCT in Google Sheets
  3. Compare Sumifs, Sumproduct, and Dsum with Examples in Google Sheets
  4. Chart to Learn Text, Date, Numeric Criteria in Sumproduct Function in Google Sheets
  5. How to Do a Case Sensitive Sumproduct in Google Sheets
  6. How to Use OR Condition in SUMPRODUCT in Google Sheets
  7. How to Use Wildcards in Sumproduct in Google Sheets
  8. How to Use Sumproduct with Merged Cells In Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

3 COMMENTS

  1. Hi,

    Would you please advise how to use the SUMPRODUCT function for hidden rows/ filtered data with an example?

    I am trying to calculate the weighted average of filtered data.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.