Sum Product Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

Why does

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

work but

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

doesn't?
 
Try it this way:

=SUMPRODUCT(--(D25:D3000="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
 
JimS said:
Why does
=SUMPRODUCT(--(D25:D3000="reds")*(G25:G3000="x"),(M25:M3000))
work but
=SUMPRODUCT(--(D25:D3000="reds"),(G25:G3000="x"),(M25:M3000))
doesn't?
Click to expand...

First, you can simply the working form, namely:

=SUMPRODUCT((D25:D3000="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.
 
Back
Top