Skip to Content

How to Use the SUMPRODUCT Function in Google Sheets

When you need to sum the products of two different columns or arrays, there is a built-in function in Google Sheets called the SUMPRODUCT function that will come in handy,

In this tutorial, I will show you how to use the SUMPRODUCT function in Google Sheets

Table of Contents

SUMPRODUCT Syntax

The SUMPRODUCT function will calculate the sum of the products of data in 2 ranges or arrays of equal size.

The syntax of the SUMPRODUCT function is:

SUMPRODUCT(array1, [array2, …])

  • array1 – the first range or array that contains the entries that will be multiplied with the corresponding entries in the second range or array
  • array2 – the second range or array that contains the entries that will be multiplied with the corresponding entries from the first range or array. This argument is optional and if it is not supplied it will be set to a range of {1,1,1…} the same size as array1

SUMPRODUCT formula example 1

To multiply the corresponding entries of each range together and sum the products without using the SUMPRODUCT function we would have to do multiple steps like this:

SUMPRODUCT formula example 2

In the above example we have multiple formulas multiplying rows together, and then a SUM function to sum the results together.

This is all done in one function with SUMPRODUCT, which is why it is so useful.

Using the SUMPRODUCT Function

Now that we’ve covered the basics of the formula, let’s look at some real-life examples to show why the SUMPRODUCT function is useful.

In this first example, I have the sales quantity and prices sold at of various products and I want to get the total revenue generated. With the SUMPRODUCT function, I can achieve this with one simple formula:

SUMPRODUCT sales

You can also take this one step further by using multiple ranges or arrays in your formula.

For example in the above scenario, let’s say I want to calculate the total sales tax for each item sold. I could also do that with SUMPRODUCT by adding in another range for the sales tax like this:

SUMPRODUCT multiple columns.1

You can see that I just have to put the new range into my formula to make calculations that would normally require multiple steps. That is why this formula is so useful.

Closing Thoughts

The SUMPRODUCT function is a very important function for spreadsheet users to know.

We’ve only scratched the surface of ways that it can be used in this tutorial, there are certainly more advanced ways to use it, but that is beyond the scope of this article.

More Google Sheets Tutorials:
How to Calculate Weighted Averages
How to Do Math Formulas
How to Calculate Running Total