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…
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)
–(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}
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.
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines