What is Sumproduct()
Multiplies corresponding components in the given arrays, and returns the sum of those products.
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…
Breaking it down
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)
Break it down further
The other tests on the date and paidcash return a similar set of 1/0, finally resulting in
Why use Sumproduct()
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.
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.