This is my favorite function – you can do so much with it. What I like about most though, it is that it seems to have been added to excel as a kind of afterthought – its behavior is weird compared to everything else. It acts as an array function without needing to be entered as one. In fact sumproduct() is simply an array version of sum(), but without the need for it to be entered as an array formula. My theory is that they found that nobody was understanding array formulas, so they took the most common potential usage- namely sum() , and created a way to use it without anyone having to know they were using an array behind the scenes. Once you are comfortable with the use of sumproduct, I guarantee that you will suddenly understand array formulas and be able to use them with ease- opening up a whole new level of excel competence. On the other hand, it is very inefficient and can really slow down your worksheet calculations (for how to identify whats slowing your workbook down look here Execution Time Logging. There are more efficient methods to achieve the same thing, such as SUMIFS() in Excel 2007, but here’s some comments on the old fashioned way.
What is Sumproduct()
Well here’s the Microsoft description – which doesn’t really do it justice at all.
Multiplies corresponding components in the given arrays, and returns the sum of those products.
Syntax
SUMPRODUCT(array1,array2,array3, …)
Array1, array2, array3, … are 2 to 255 arrays whose components you want to multiply and then add.
An example of where you could use it
Lets get back to our example we’ve been using to learn about named ranges. Please make sure you have worked though and understood Named Ranges with Column Headings as we’re going to build on the work from that.
First update your sheet to look like this…
and create MyExpensesItems, MyExpenses, MyExpensesDate, MyExpensesPaidCash according to the method in Named Ranges with Column Headings Create a report that looks like this, making sure the date headings are a date format. This report could be created with a pivot table somehow, but we are going to use sumproduct() in cells I3:K5 instead
Breaking it down
Lets look at how these cells are calculated
The formula in cell I3:
=SUMPRODUCT(–(myExpensesItems=$H3)* –(MyExpensesPaidCash=”Yes”)* –(MyExpensesDate<DATE(YEAR(I$2),MONTH(I$2)+1,1))* –(MyExpensesDate>=DATE(YEAR(I$2),MONTH(I$2),1))* MyExpenses)
Each of theses is going to return a series of true/false results for every one of the rows in the expenses block data, which will be converted into a series of 1/0 and multiplied with the each cell of MyExpenses. The result of this is that if any test is false (converted to 0), the overall result will be 0 * MyExpenses, and if all are true the result will be 1 * MyExpenses.
Break it down further
SUMPRODUCT would return {true;false;false;true;false} for the test below. (Item 1 and 4 match $H3 – “Sugar”). The operation — is performed on that result (this is just shorthand for -1 * -1 *), and is a quick way to coax True/False to become 1/0, so the new result is {1;0;0;1;0}.
–(myExpensesItems=$h$3)
The other tests on the date and paidcash return a similar set of 1/0, finally resulting in
{1;0;0;1:0}*{1;1;1;0;1}*{1;0;0;0;0}*{1;0;0;0;0}*{12.32;18.23;23.35;9.20;7.67}
When all these are multiplied together, the result is 12.32. (only the 1st item has all values <> 0.) To complete the report, fill the sumproduct() formula down from I3 to K5 and add the sum() for each of the total rows and columns. You should get the results shown earlier.
Why use Sumproduct()
This is a very powerful function, especially when used in conjunction with dynamic named ranges. The potential to summarize data throughout a sheet by referring back to complex data structures and tests and without the need to use pivot tables makes this combination and extremely powerful one.
Because this is a powerful command it can be quite difficult to debug, so using the Evaluate Formula in the Excel Formulas Ribbon is a good way to track down what is happening step by step.
I’ll do further pages on sumproduct() as you can use it for very strange things – that’s why it’s my favorite
For help and more information join our community, follow the blog, follow me on twitter, or follow me on g+
In the meantime why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.