Explanation
The goal is to sum the quantity for rows where the color is “Red”, the region is “East”, and the quantity is greater than 7. Although there are a number of ways to solve this problem in Excel purpose of this example is to demonstrate how to replace a nested IF with a single IF using Boolean logic. This technique can be used to reduce the complexity of certain formulas. However, the example is for illustration only. This particular problem could be easily solved with SUMIFS or SUMPRODUCT .
Nested IF approach
One way to solve this problem is by using three separate IF formulas like this:
IF(color="red",IF(region="east",IF(quantity>7,quantity)))
Each IF statement generates an array of TRUE and FALSE values like this:
=IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE},
IF({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE},
IF({FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},quantity)))
Only quantities where all three logical tests return TRUE “survive” the operation. Other quantities become FALSE and are evaluated by SUM as zero. The final result inside the SUM function is an array of values like this:
=SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;FALSE;10})
The SUM function ignores the FALSE values and returns 18 as a final result.
Boolean logic approach
Another way to solve this problem is with Boolean logic . In the worksheet shown, we have this formula:
=SUM(IF((color="red")*(region="East")*(quantity>7),quantity))
Notice this formula contains just one IF function. Inside, IF, the logical test is this part:
(color="red")*(region="East")*(quantity>7)
Each expression generates an array of TRUE and FALSE values:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}*{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
When these arrays are multiplied together, the math operation coerces the TRUE and FALSE values to 1s and 0s. The result is a single array inside the IF function like this:
IF({0;0;0;0;0;0;1;0;1},quantity)
The array of 1s and 0s works to filter out quantities that don’t meet all three conditions, and the result from IF is the same as we saw above with the nested IF approach:
=SUM({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;FALSE;10})
As before, SUM returns a final result of 18.
Without the IF function
It is possible to remove the IF function altogether and use only Boolean algebra like this:
=SUM((color="red")*(region="East")*(quantity>7)*quantity)
This formula works fine, but it must be entered as an array formula in Excel 2019 and older. To avoid that requirement, you can switch to the SUMPRODUCT function :
=SUMPRODUCT((color="red")*(region="East")*(quantity>7),quantity)
=SUMPRODUCT((color="red")*(region="East")*(quantity>7)*quantity)
Both formulas above return the same result and don’t require special handling in older versions of Excel since SUMPRODUCT can handle many array operations natively . For a more detailed example, see SUMPRODUCT with IF
Note: Boolean logic works especially well in formulas that sum values, because non-matching values result in zeros, and zeros do not affect the final sum. However, if the goal is to average values or get the minimum value based on one or more conditions, the Boolean approach can cause an incorrect result because the 0s may affect the final calculation.
Explanation
The goal is to demonstrate how other formulas and functions can be nested inside the IF function . The example is a simple quantity-based discount formula.
IF function
The IF function evaluates a logical test and returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:
=IF(logical_test,value_if_true,value_if_false)
For example, if cell A1 contains the value 75, then you could use IF to return “Pass” or “Fail” like this:
=IF(A1>70,"Pass","Fail") // returns "Pass"
If the value in A1 is 65, then the same formula will return “Fail”:
=IF(A1>70,"Pass","Fail") // returns "Fail"
What is not obvious with IF is that the logical_test , the value_if_true, and the value_if_false can all be other formulas . The example below shows how this works.
Example
In the worksheet shown, the goal is to apply a simple quantity-based discount to the total calculated in column E. If the quantity is greater than 20, we want to discount the total by 10%. Otherwise, we want to calculate the total normally. In cell E5, the formula used to perform this task is:
=IF(C5>20,C5*D5*0.9,C5*D5)
The formula works like this:
- The logical test is C5>20. This checks if the quantity of items sold (in cell C5) is more than 20.
- The value if true is C5D50.9. This calculates the total price as the quantity sold times the price per item and then applies a 10% discount by multiplying the result by 0.9.
- The value if false is C5*D5. This calculates the total price as the quantity sold times the price per item, without any discount.
So, if more than 20 items were sold, the formula applies a 10% discount to the total price. Otherwise, it just calculates the total price without any discount. You can then copy the formula down column E to apply it to all items in the spreadsheet.
Other calculations
The calculations used inside the IF function can be customized as needed. For a more general formula that applies the discount itself, you can use the following:
=IF(C5>20,C5*D5*(1-discount),C5*D5)
For example, to apply an 18% discount, you would use:
=IF(C5>20,C5*D5*(1-18%),C5*D5)
This works because Excel will automatically evaluate the percentage 18% as the number 0.18. You can also adjust calculations in the logical test as needed. To apply a 20% discount to apples only, you could use the AND function in the logical test like this:
=IF(AND(C5>20,B5="apples"),C5*D5*(1-20%),C5*D5)
In Excel, nesting other calculations inside a function or formula is a common practice in many more advanced formulas. You can find many examples in this list .
Detailed quantity-based calculation
If you are interested in a more detailed example of a quantity-based discount formula, this example uses XLOOKUP instead of IF to apply more granular discounts based on a lookup operation.
