SUMPRODUCT

[Mike]

Hi.

In SUMPRODUCT, is there a way to specify the arrays to
cover non-sequential cells? For instance, I want to use
sumproduct by specifying the following arrays:

Array1 --> A1,A7,A13,A20
Array2 --> B1,B7,B13,B20

Thanks,
Mike

 

[J.E. McGimpsey]

one way:


=SUMPRODUCT(A1:A20,B1:B20,{1;0;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;1})

 

[Mike]

Thanks for the reply! I had two follow-up questions:


1. What does the {1;0;0 .... 0;1} do?
2. Is there a way to modify this statement so that it
will count the occurrences when the first array = "John"
and the second array has the number "0"?

Thanks again,
Mike.

 

[J.E. McGimpsey]

The array {1;0...;1} serves to mask the unwanted cells. When the
arrays are multiplied, A1 is multiplied by B1, A2 by B2, etc. That
result is then** multiplied by the values in the constant array:

A1 * B1 * 1 ==> A1 * B1
A2 * B2 * 0 ==> 0
...
A20 * B20 * 1 ==> A20 * B20

the results are then added. Anywhere that the constant array has a
zero will be excluded from the sum, even if An * Bn <> 0.


2. One way:

=SUMPRODUCT(--(A1:A20="John"), --(B1:B20=0),
{1;0;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;1})

Since SUMPRODUCT requires numeric arrays, the boolean (TRUE/FALSE)
array returned by (A1:A20="John") has to be coerced to numeric. The
Unary Minus operator (or multiplying the array by 1 - any math
operation) coerces TRUE/FALSE to 1/0 and inverts it. The second
Unary Minus inverts that result (-(-1) ==> 1, -(-0) ==> 0).


**I don't know the internal workings of SUMPRODUCT - I suspect that
the all the arrays are multiplied at one time, but for purposes of
explanation, I'll assume they're multiplied sequentially.

 

[Mike]

Thank you so much!

Mike.

-----Original Message-----
The array {1;0...;1} serves to mask the unwanted cells. When the
arrays are multiplied, A1 is multiplied by B1, A2 by B2, etc. That
result is then** multiplied by the values in the constant array:

A1 * B1 * 1 ==> A1 * B1
A2 * B2 * 0 ==> 0
...
A20 * B20 * 1 ==> A20 * B20

the results are then added. Anywhere that the constant array has a
zero will be excluded from the sum, even if An * Bn <> 0.


2. One way:

=SUMPRODUCT(--(A1:A20="John"), --(B1:B20=0),
{1;0;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;1})

Since SUMPRODUCT requires numeric arrays, the boolean (TRUE/FALSE)
array returned by (A1:A20="John") has to be coerced to numeric. The
Unary Minus operator (or multiplying the array by 1 - any math
operation) coerces TRUE/FALSE to 1/0 and inverts it. The second
Unary Minus inverts that result (-(-1) ==> 1, -(-0) ==> 0).


**I don't know the internal workings of SUMPRODUCT - I suspect that
the all the arrays are multiplied at one time, but for purposes of
explanation, I'll assume they're multiplied sequentially.



.