One Friday morning, while helping my colleague Paul analyze a large sales file, I realized he was multiplying calculation columns just to check if a sale met several criteria. What if I had shown him how to simply nest IF with AND and OR? Since then, he swears by these formulas. Try it yourself: you will save time and gain clarity!
Why combine IF, AND, and OR?
In many cases, a simple IF formula is not enough if you need to check two, three, or even four conditions before returning a result. Imagine you want to mark a sale as “priority” only if the amount exceeds €1,000 and the customer is loyal, or if the manager has already approved the order. With IF+AND+OR, everything becomes possible in a single cell, without intermediate columns or unnecessary complexity.
Basics of the syntax
The IF function
We start with the basics: the IF function is written as follows: =SI(test_logique; valeur_si_vrai; valeur_si_faux). It tests a condition and returns one of two results depending on whether the condition is met or not. It’s simple but worthy of a Swiss army knife for basic needs.
The AND and OR functions
Next come AND and OR, whose syntax is identical: =ET(test1; test2; …) and =OU(test1; test2; …).
• AND returns TRUE if all conditions are true.
• OR returns TRUE if at least one condition is true.
Combining these operators with IF allows managing complex logic.
Practical examples
Example 1: validation of school grades
Suppose you have a table of grades and want to display “Pass” if the student has an average ≥ 10 and no grade < 8, otherwise "To review". Here is the formula:
=SI(ET(moyenne>=10; MIN(note1;note2;note3)>=8);"Réussite";"À revoir")You will notice that MIN() is used to quickly check that no grade falls below the threshold. Guaranteed readability gain.
Example 2: calculating bonuses based on multiple criteria
In my department, a bonus is given if:
- the turnover exceeds €20,000 and the customer satisfaction rate is ≥ 90%;
- or if the manager has manually approved the exemption.
Possible formula:
=SI(OU(ET(ca>20000; satisfaction>=90); validation="OK"); prime_calc; 0)On the maintenance side, this format remains clear. If you need to retrieve information from another sheet, you can precede this with a small VLOOKUP to get the standard bonus according to the performance level.
Nested formulas without getting lost
By adding IF statements inside other IF statements, you eventually get lost. This is where AND and OR avoid nests of nested IFs. Look at this table summarizing three situations:
| Situation | Formula | Explanation |
| Basic approach | =SI(A2>1; "OK"; "KO") |
Only one criterion, simple and direct. |
| Multiple criteria | =SI(ET(A2>1; B2<5); "OK"; "KO") |
Two combined tests without nesting. |
| Alternative options | =SI(OU(A2>10; B2>20); "Alerte"; "R.A.S.") |
At least one condition triggers the alert. |
Tips and best practices
- Name your ranges: using names (e.g.
SeuilCA) makes reading easier. - Test step by step: break down your logic, validate each part before nesting.
- Use
SI.NOin Office 365/2021 to avoid multiplying parentheses. - Document your formulas: add a comment to the cell to explain the overall purpose.
- Think performance: thousands of calls to complex formulas can slow down the workbook.
FAQ
Q: Can you nest more than 64 IF conditions?
A: In recent versions of Excel, the number of nesting levels has been reduced thanks toSI.NO. In practice, it is better to turn to AND/OR than to stack.
Q: How to handle more than two conditions with AND?
A: You can pass as many tests as necessary to AND or OR, for example=SI(ET(A1;A2;A3;A4);"Vrai";"Faux").
Q: Does AND() or OR() accept text as a test?
A: Yes, a test can beB2="Oui"or a string comparison, Excel considers this as TRUE or FALSE.
In summary, combining IF, AND, and OR in Excel is the key to formulas that are both powerful and readable. No need to create ten columns to check each criterion: a single calculation concentrates all business rules, maintainable and efficient. So don’t hesitate any longer, test these formulas on your data and see the clarity gain!