Skip to content

Understanding the SUMPRODUCT Function

In this article, we will explain how the SUMPRODUCT function works.

The SUMPRODUCT function adds together (sums) the result of groups of numbers that have been multiplied together (the product).

Continue reading for a much better understanding of how this all works.

How the SUMPRODUCT Function Works

The SUMPRODUCT function accepts two or more arrays (lists of numbers) and does two things.

  1. It multiplies the numbers that share the same position in each array
  2. It adds together the results from each multiplication

Let’s take a look at each of these steps to better understand what’s going on.

Multiplying Numbers That Share the Same Position

Let’s say we set up the SUMPRODUCT function to work with the following two arrays.

The first thing the function will do is multiply the numbers that share the same position. In other words, the first number in Array 1 will be multiplied by the first number in Array 2.

The second number in Array 1 will be multiplied by the second number in Array 2.

And the third number in Array 1 will be multiplied by the third number in Array 3.

Adding Together the Results From Each Multiplication

After all of the multiplications are performed, the SUMPRODUCT function will then add together all of the calculated products.

This yields the final result calculated by the SUMPRODUCT function.

One More Example

We will cover one more example to nail down your understanding of the SUMPRODUCT function. Take a look at the three arrays the SUMPRODUCT function will work with this time.

Remember that the SUMPRODUCT function can work with two or more arrays, so three arrays are no problem.

You may also notice that the arrays are set up in individual rows this time instead of columns. This will also work just fine with the SUMPRODUCT function.

Working Through the Example

Just as before, the SUMPRODUCT function will start by multiplying the numbers in each array that are in the same position.

Finally, just as before, the calculated products are added together to return the final result.

As you can see, the SUMPRODUCT function calculates exactly what was described.

And that’s how the SUMPRODUCT function works in both Google Sheets and Microsoft Excel.

This Post Has One Comment

  1. This is so neat! I see this come up in auto fill all the time, but I never really knew what it did! I can’t wait to start using this!

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Back To Top