IF with boolean logic

This formula shows how a nested IF formula can be replaced with Boolean logic to sum values based on three conditions. In the example shown, the formula in F5 is: {=SUM(IF((color="red")*(region="East")*(quantity>7),quantity))} where color (B5:B13), region (C5:C13), and quantity (D5:D13) are named ranges. The result is 18, the sum of quantity for rows where the color is "Red", the region is "East", and the quantity is greater than 7. Note: In Excel 2019 and earlier, this is an array formula, and must be entered with control + shift + enter.

April 14, 2025 · 1 min · 85 words · Kristin Powell

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 withSUMIFSorSUMPRODUCT.

Excel formula: SUMPRODUCT with IF

Other quantities become FALSE and are evaluated by SUM as zero.

Boolean logic approach

Another way to solve this problem is withBoolean logic.

In the worksheet shown, we have this formula:

Notice this formula contains just one IF function.

Excel IF function

More than one condition can be tested by nesting IF functions.

The IF…

Boolean logic approach#

Related videos#

Intro to Boolean Logic#

Boolean logic approach

Related videos

Intro to Boolean Logic