what does the "- -" mean in this formula?

  • Thread starter Thread starter jane
  • Start date Start date
J

jane

=SUMPRODUCT(--(Sheet1!$B$2:$B$5=Sheet1!$C11),--(Sheet1!$C$2:$C$5=E$2),Sheet1!$G$2:$G$5)

What does the "- -" mean in the formula above? This is a new formula for me
and want to be sure I fully understand how it works... the "- -" is the only
part throwing me off.

thanks for your help on this.
take care, Jane
 
=SUMPRODUCT(--(Sheet1!$B$2:$B$5=Sheet1!$C11),
--(Sheet1!$C$2:$C$5=E$2),Sheet­1!$G$2:$G$5)

What does the "- -" mean in the formula above?
Click to expand...

It really has no special meaning. It is simply double negation. For
example, --2 is 2; ---2 is -2; ----2 is 2; etc.

However, the effect of using double negation is circumstance such as
this is to cause Excel to treat a boolean value (true or false) as a
number (1 or 0).

Using the boolean term in any arithmetic expression will have the same
effect. The above could have been rewritten without the use of double
negation, for example:

=SUMPRODUCT((Sheet1!$B$2:$B$5=Sheet1!$C11) *
(Sheet1!$C$2:$C$5=E$2), Sheet­1!$G$2:$G$5)

or even:

=SUMPRODUCT((Sheet1!$B$2:$B$5=Sheet1!$C11) *
(Sheet1!$C$2:$C$5=E$2) * Sheet­1!$G$2:$G$5)

Another alternative, returning to the original form:

=SUMPRODUCT(1*(Sheet1!$B$2:$B$5=Sheet1!$C11),
1*(Sheet1!$C$2:$C$5=E$2), Sheet­1!$G$2:$G$5)

HTH.
 
Back
Top