Sumproduct

N

Naraine Ramkirath

Question: Can someone explain the sumproduct function? when would you use
it? what are the advantages/disadvantages of using this function.

Naraine
 
A

AKphidelt

Basically it sums up true/false statements

So something like =SUMPRODUCT((A1:A10="red")*(B1:B10="blue"))

This breaks down simply to

If A1 = Red, check if B1 = Blue
If A2 = Red, check if B2 = Blue

If A1 = Red and B1 = Blue then it is True = 1

So it counts how many times it's true.
 
J

JE McGimpsey

Basically it sums up true/false statements

Not really -

It multiplies arrays together, then sums the resulting array.

IF you happen to use conditional statements that return TRUE/FALSE, and
coerce them to numbers (1/0) either by using a math operator (in which
case the multiplication happens before the result is passed to
SUMPRODUCT), or by explicitly coercing, and useing the SUMPRODUCT
syntax, e.g.:

=SUMPRODUCT(--(A1:A10="red"),--(B1:B10="blue"))

then it can be used in the way you say.

But it's far more useful than just summing true/false.
 
Top