Tips for Excel, Word, PowerPoint and Other Applications

Introduction to SUMPRODUCT

Why It Matters To You

I consider SUMPRODUCT to be one of the handful of must know functions. It's basic functionality is nice enough, but once you learn the fundamentals and start to explore the more advanced capabilities, you'll never look back. SUMPRODUCT is even more powerful if you put a little bit of thought into the layout of your data to maximize its effectiveness.

How to ...

SUMPRODUCT simply calculates the sum of the products of corresponding arrays. This means the product of the 1st values in each range is calculated, then added to the product of the 2nd values ... , then added to the product of the nth values.

At this level, it's fairly straightforward. Just select at least 2 equally sized ranges and off you go. If you want to see the spreadsheet with the examples below, just click here: sumproduct_basic.xls

Here is the simplest form of the SUMPRODUCT, two equal columns of data.

Your arrays have to be the same dimensions. If they aren't equal, you will end up with a #VALUE error.

However, this does not mean that your arrays have to be a single column or row. In this example, I'm taking the SUMPRODUCT of two tables. SUMPRODUCT simply multiples the values in the same relative position together then sums all the products.

You can also use more than two arrays on a SUMPRODUCT.

One of the applications for which I use SUMPRODUCT is the calculation of weighted scoring. This is just a practical application of Example 1. Note that I added up all of my weights. I do this out of habit as a check to ensure my weights total to 100%.

The other common use is to SUMPRODUCT quantities and unit costs to calculate the total cost of a bundle of goods, in this case, my grocery list.

As a small refinement, I've added a budget line and calculated the difference. Now we've made ourselves a mini-model that I can hand off to someone else to figure out how we optimize our $30.

Lastly, here's an application of 3 arrays in a SUMPRODUCT.

I hope this has been straightfoward enough for everyone.

Notes

Last updated	9/4/07
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	Advanced SUMPRODUCT