sumproduct
 

Hi, could somebody explain why the '-' in
=SUMPRODUCT(-(F2:F14="S"),-(G2:G14<8)), make such a difference. Used
without, which I was doing until Iposted here earlier, doesnt return
the same results.
Regards Robert

sumproduct
 

The result of the comparisons, (F2:F14="S") and (G2:G14<8), are Boolean
values, either TRUE or FALSE. The minus sign tells Excel to use the negative
numeric value (TRUE = 1 and FALSE = 0), so the '-' operator coerces a TRUE
to -1 and FALSE to 0.

This particular formula works because there are two (or any even number) '-'
operators, and a negative times a negative returns a positive result. If a
third (or any odd number) condition, say -(H2:H14<10) were added, the
formula would return a negative result because a negative times a negative
times a negative returns a negative.

Far better is to use the common technique of using '--' which forces each
comparison to negative, then back to positive (the negative of a negative is
a positive).

=SUMPRODUCT(--(F2:F14="S"),--(G2:G14<8))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"RobcPettit" wrote in message
oups.com...
Hi, could somebody explain why the '-' in
=SUMPRODUCT(-(F2:F14="S"),-(G2:G14<8)), make such a difference. Used
without, which I was doing until Iposted here earlier, doesnt return
the same results.
Regards Robert