Excel's SUMPRODUCT Formula: Understanding & Using It
Today, we will discuss the Excel SUMPRODUCT formula - one of the more handy ones in Excel’s engine-room which is literally teeming with formulas of all kinds. Not only are there formulas for different aspects of numbers, these formulas can be used in ways not thought of by the coders themselves. Fertile ground, in short, for a lifetime of learning.
Here’s the one line description that Excel offers in terms of explanation for the formula:
“Multiplies corresponding components in the given arrays, and returns the sum of those products”
Yes, a very dry and drab description… But hey, that’s why we are here! Here’s a little bit of an extended explanation.
Say you have a range of numbers in column A, and another range of numbers in column B.
Well, what Sumproduct allows you to do is, take the array (an array is best thought of as data stored in more than one cell) that reposes in columns A, and multiply it with those in columns B. The sum of the individual products is what the Sumproduct formula returns.
Have a looky at the data given below. If you have time on your hands, you could go ahead and try and compute the following (1*6) + (2*7) + (3*8) +(4*9) + (5*10).
Or, you could just use Sumproduct instead, and know that the answer is 130 before… well, before anybody using Sumproduct does.
Well, good for you, the cynics among you might be thinking. And so what?
Well, you could make use of this formula to save you a lot of time. Calculating sales volume over a year for 500 salesmen over 12 months might leave you hunting for a pivot table or array, but the sumproduct forumla is another, often easier way.
If you would like to learn many more such tips, and to become dazzlingly proficient at Excel, try these top Excel tutorials.
Chandoo does a particularly good job of explaining just how that is done.
And that’s just the simplest application. People have gone ahead and done some seriously cool stuff with the Sumproduct formula. If your knowledge of Excel goes beyond the intermediate stage, do have a look at the following:
Charley Kyd explains how to go about using the Sumproduct formula to find the last item in a list. Contrary to what he says on that page, there are a couple of other ways to go about it as well, but the Sumproduct method is certainly one of the easier ones.
Jason Khoo, another Excel whiz, points out that the Sumproduct formula is made extra cool because of its ability to handle multiple conditionals – not something that every formula can do.