Microsoft Office Tutorials and References

In Depth Information

**WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE**

Alternate Strategy:
In fact, all the following operations also convert an array

of
TRUE/FALSE
to an array of
1/0
:

N(C4:C14>20000)

1*(C4:C14>20000)

(C4:C14>20000)+0

(C4:C14>20000)^0

You could multiply the criteria terms together, replace the comma with an

asterisk, and let Excel perform all the logical tests. The formula to calculate the

bonus would be:

=SUMPRODUCT((C4:C14>20000)*(D4:D14>0.5),C4:C14)*0.02

This syntax allows you to combine
AND
and
OR
logic. Say that you want to pay

the bonus if both conditions are met or if the rep is Joey. You would add some

parentheses and indicate that the bonus is also paid when the rep is Joey:

=SUMPRODUCT(((C4:C14>20000)*(D4:D14>0.5))+(B4:

B14="Joey"),C4:C14)*0.02

Figure 12 shows a formula that conditionally sums based on two
AND
and one

OR
criteria.

Figure 12.
You can build the Boolean logic as one term of the
SUMPRODUCT
function.

Summary:
To use logical tests in
SUMPRODUCT
, you can convert the
TRUE/

FALSE
values to
1/0
values by using minus minus or other methods described

in this topic.

Source:
http://www.mrexcel.com/forum/showthread.php?t=221125 and http://

www.mrexcel.com/forum/showthread.php?t=128907