General SUMPRODUCT/ARRAY Questions

[Bob Phillips]

Alright. I have searched and searched. The excel help file is silly.

It suffers the problem of many, most, help files in that it tells you how to use it in its most basic form, it does not even attempt to explain the different situations it can handle.

Also, MS are not even properly aware of the way that we use SP. I have spoken to technical help writers at MS who knew nothing of what that paper I pointed you at explains.

All I want to do is confirm that when we use two Multidimensional Arrays in SUMPRODUCT the order of operations is indeed what I belive it is.

Question 1: Is the following correct?
By playing around with it, it seems that if I use two 2 x 2 Arrays as my arguments, then SUMPRODUCT(Array1, Array2) will do the following:

Multiply corresponding elements of each array, which would, in theory, yield another 2 x 2 Array. And then it SUMS all of the elements of the resultant Array.

For example:

Given two 2 x 2 Arrays
A = {a, b ; c , d}
B = {w, x ; y, z}

Then SUMPRODUCT(A , B) = (a*w) + (b*x) + (c*y) + (d*z)

I know it's a stupid question, but I keep getting confused with Matrix Multiplication which returns an Array (as opposed to a Scalar).

Yes, that is correct.

Question 2:
DO we ever use Ctl + Shift +Enter with SUMPRODUCT? [I don't think we do..but I am not positive]

If there is an array operation within one of the conditions, it is necessary to C-S-E it, but in that case, you might just as well use SUM as SUMPRODUCT.

Question 3:
What happens when we do not use Ctl + Shift + Enter with MMULT() ? [I am under the impression that it will just return ONE element of the resultant Array]

MMULT in its essnce is not a C-S-E formula, it multiplies n columns by n rows and retunrs a scalar.

What is the issue you are referring to?

Question 4:
By ANDing (i.e. using '*') You can SUMPRODUCT two Arrays of unequal Row-length so long as they
have the same Column-Length....right?

For Example:

 
SUMPRODUCT((A1:A4)*(B1:C4))

is EQUAL TO

SUMPRODUCT(A1:A4, B1:B4) + SUMPRODUCT(A1:A4, C1:C4)

correct?

You are correct in your final analysis, but incorrect in how you got there. The * is not an AND here because you do not have conditions so you are not returning TRUE/FALSE arrays. * is pure multiplication here, but multiplying array elements.



Question 5:

Can all of the SUMPRODUCT 'tricks' be applied to VBA SUMPRODUCT?[/QUOTE]