=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

It just converts the thing in the parenthesis to an array of numbers rather
than an array of logicals.

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200826


"jane" wrote:

=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

On Jan 14, 3:27*pm, jane wrote:
=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?

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.