Sum Product Question

[JimS]

Why does

=SUMPRODUCT(--(D253000="reds")*(G25:G3000="x"),(M25:M3000))

work but

=SUMPRODUCT(--(D253000="reds"),(G25:G3000="x"),(M25:M3000))

doesn't?

 

[Pete_UK]

Try it this way:

=SUMPRODUCT(--(D253000="reds"),--(G25:G3000="x"),(M25:M3000))

The double unary minus converts the Trues and Falses to 1s and 0
respectively, but in your second example the second term did not have
this operation.

Hope this helps.

Pete

 

[JoeU2004]

Why does
=SUMPRODUCT(--(D253000="reds")*(G25:G3000="x"),(M25:M3000))
work but
=SUMPRODUCT(--(D253000="reds"),(G25:G3000="x"),(M25:M3000))
doesn't?

First, you can simply the working form, namely:

=SUMPRODUCT((D253000="reds")*(G25:G3000="x"),M25:M3000)

The reason the second form returns an error is that the second argument,
(G25:G3000="x"), returns a boolean (TRUE, FALSE) instead of a number.
SUMPRODUCT requires the latter.

The purpose of "--" is to convert a boolean result into a numeric one. The
"--" is double negation; for example, 5 and -5 are different, but 5 and --5
are the same: -(-5).

But any arithmetic operation involving boolean values will accomplish the
same goal. That is why no "--" is needed in the first form; multiplication
("*") is sufficient.