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.
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+
You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my book, Going Gas – from VBA to Apps script.
Next SumProduct topics: