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

Search JabSto ::

Custom Search