SUMPRODUCT handling of irregular length arrays; Seeing the intermediate results

[polarisking]

Yesterday I asked for help on, and received several excellent solutions, for a sumproduct issue. The SUMPRODUCT expression contains 3 arrays where 2 are the same length, and 1 is a different length. The formula works - I want to understand what's happening under the covers.


If I attempt to use the Evaluate Formula tool, the arrays are displayed but results are very long and the Evaluate window is very small. I'm not able to copy the evaluated results and paste them elsewhere for a bigger, better view.


Any ideas?

 

[Luke M]

Example setup:

[pre]

Main List (A2:C2)
apple	brocolli   orange

Table (A5:B6)
apple	1
orange	2

[/pre]
Example formula:

=SUMPRODUCT((A2:C2=A5:A6)*(B5:B6))


Link to previous discussion


Now, to explain how it works. The trick is that you are taking a horizontal array and multiplying by a vertical array. In the Evaluate Formula tool, the difference is noted by whether the numbers are separated by commas or semicolons.


So, when you take the 1x3 horizontal array and multiply by the 2x1 vertical array, you'll get a 2x3 array. In this case, it's filled with True/False outputs.


This operation follows matrix multiplication rules, with each item in the 1x3 array getting multiplied by each item in first column (aka, the only column) in the 2x1 array. So, our first item of apple gets checked against everything in the list, then brocolli, then orange, producing an array like this:


True,false,false;

false,false,true


Next, this 2x3 array gets multiplied by the 3rd array, also a 2x1. This will cause every True in the first row to be filled with the value in first row of the 2x1, and every true in 2nd row to be filled with 2nd item in 2x1, and so on. Thus, our array becomes:

1,0,0;

0,0,2


Note that all of the False's get converted to 0 when we multiply. The SUMPRODUCT function then add all the components together and gives us our result, of 3.

 

[Hui]

You may also want to read:

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/