Explanation
The goal is to mark rows where the color is “Red” AND the size is “Small” or “Medium”. To perform this task, you can use the IF function in combination with the AND function and the OR function .
IF function
The IF function runs a test, then 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"
Notice that the text values inside IF must be enclosed in double quotes ("").
AND function
The AND function returns TRUE if all conditions are TRUE. For example, if cell A1 contains “Red” and B1 contains 10, then this formula returns TRUE because both expressions return TRUE:
=AND(A1="Red",B1>5) returns TRUE
However, this formula returns FALSE because the second condition returns FALSE:
=AND(A1="Red",B1>12) returns FALSE
Because the AND function returns TRUE or FALSE, it often appears inside the IF function as a logical test, specifying the conditions to be checked.
OR function
The OR function returns TRUE if any condition is TRUE. For example, if cell A1 contains “Red” and B1 contains 10, then this formula returns TRUE because both expressions are TRUE:
=OR(A1="Red",B1>5) returns TRUE
However, if only the first condition is met, the formula will still return TRUE:
=OR(A1="Red",B1>12) returns TRUE
This formula returns FALSE because both conditions are FALSE:
=OR(A1="Blue",B1>12) returns FALSE
AND with OR
The AND function can be combined with the OR function. In the example shown, we want to identify records where the color is Red and the size is Small or Medium. This means we need to test the value in column B for “Red” and test the value in column C for “Small” or “Medium”. We can do that by nesting the OR function inside the AND function like this:
AND(B6="red",OR(C6="small",C6="medium"))
Note: Excel formulas are not case-sensitive by default, so B6=“red”, B6=“Red”, and B6=“RED” will all return the same result. For that reason, the text values in the logical test are left in lowercase.
The formula above will return TRUE only if the value in B6 is “Red” AND the value in C6 is “Small” OR “Medium”. In any other case, the formula will return FALSE. The formula evaluates the side function first, working outwards. The OR function returns a result to AND, and the AND function returns the final result. This is exactly what we need for the logical test inside IF.
Final formula
To process the result from AND and mark a row with an “x” when both conditions are TRUE, we embed the AND formula inside the IF function as the logical test. In D6, the formula is:
=IF(AND(B6="red",OR(C6="small",C6="medium")),"x","")
If the result from AND is TRUE, the IF function returns “x”. If the result is FALSE, the IF function returns an empty string (""), which looks like an empty cell in Excel. As the formula is copied down column D, the result is an “x” in column D only when the color is Red and the size is Small or Medium. The result from IF can be customized as needed.
Note: You might wonder if we need to supply an empty string ("") for the false result. Technically, this argument is optional. However, without a value, the IF function will return and display FALSE for rows that don’t meet the specified conditions.
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.
