OmniDocExcel IF Formulas for Conditions and Multiple Criteria
Write Excel IF formulas for one condition or several, using AND and OR inside IF, then check each row returns the value you expect.
Many spreadsheets need a result that depends on a condition. A grade list might need "Pass" or "Review" based on a score, a sales sheet might flag orders over a limit, and an inventory list might show "Reorder" only when stock is low and the item is still active. Typing the answer by hand works for five rows, but it breaks down once the list grows or the data changes.
This guide shows how to write Excel IF formulas that test one condition, then combine several conditions with AND and OR. It also covers the checks that catch the errors people most often miss. Everything here uses standard spreadsheet functions, so the steps work in any version of Excel that includes IF, AND and OR.
How do you apply one condition or several in an Excel IF formula? The short answer
Write the test first, then wrap it in IF, and use AND or OR inside IF when you have more than one condition. Here is the pattern for each common situation.
| Your situation | What to do | Why |
|---|---|---|
| One condition, two possible results | =IF(test, value_if_true, value_if_false) | IF checks the test and returns one of your two values |
| Several conditions that must all be true | =IF(AND(test1, test2), "Yes", "No") | AND returns TRUE only when every test passes |
| Several conditions where one is enough | =IF(OR(test1, test2), "Yes", "No") | OR returns TRUE when at least one test passes |
| Three or more possible results | Nested IF, or IFS in newer versions | Each extra test handles another outcome |
| Blank cells should not produce a result | Put a test for blank cells first | Empty input otherwise gets treated as zero or as a failed test |
Before you start
Check these things before you write the formula. Each one prevents a common cause of wrong results.
- Confirm the numbers are stored as numbers. A value that looks like 85 but is stored as text will fail a test such as >= 60. Text values usually line up on the left side of the cell, while numbers line up on the right.
- Write down the exact labels you will compare. A test like ="Active" matches only that spelling. Text comparisons in Excel ignore capital letters, but extra spaces still cause a mismatch.
- Decide where your thresholds live. Put a value such as 60 in its own cell, for example F1, so you can change it later without editing every formula.
- Know the column letters for each condition. Note which column holds the score, the attendance, or the status you are testing. Mixing up columns is a common reason a formula returns the wrong branch.
- Pick two or three test rows. Choose one row that clearly passes, one that clearly fails, and one that sits right on the boundary. You will use these rows to check the formula.
Building a multiple-criteria IF formula in Excel, step by step
The example below uses a grade list where a student passes only if the score in column B is at least 60 and the attendance in column C is at least 80. Adjust the cell references to match your own sheet.
- Write the rule in plain words first. For example: "Pass if score is 60 or more and attendance is 80 or more; otherwise Review." A clear rule makes the formula much easier to write and check.
- Click the first empty cell in your result column and type =IF(. This starts the formula. Everything after it is the test and the two results.
- Combine the conditions with AND. Type AND(B2>=60, C2>=80). Commas separate the tests, and AND returns TRUE only if both are true.
- Add the two results. Type a comma, then "Pass", a comma, and "Review", then close the bracket. The full formula reads =IF(AND(B2>=60,C2>=80),"Pass","Review").
- Press Enter and check the first row. Confirm the result matches your plain-words rule for that student before you copy the formula further.
- Copy the formula down the column. Drag the formula to the last row of data. If the formula refers to a threshold cell, lock that reference by adding dollar signs before its column letter and row number, so the reference stays fixed while the row numbers change.
To use OR instead, replace AND with OR in step 3. The formula then returns "Pass" if either test is true, which suits cases such as "Flag the order if it is over the limit or marked urgent."
Check the result
Once the column is filled, check the output before you share or filter the sheet. Use the table below to find and fix common problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Boundary row | Look at the row where the score is exactly 60 | If it returns the wrong result, change > to >= or the reverse |
| Text results | Confirm "Pass" and "Review" show without the quote marks | Quote marks missing in the formula cause a name error |
| Numbers stored as text | Check whether values align to the left | Convert the column to numbers, then re-run the formula |
| Blank rows | Scroll to the end of the list | Add a test for blank cells before the main condition |
| Error codes | Look for #NAME?, #VALUE! or #REF! | #NAME? usually means a typo in a function name; #VALUE! often means text is being used in a calculation |
| Shifted references | Click a copied cell and read the formula | A threshold reference without locking makes it drift as you copy |
📊 At a glance
Three approaches cover most condition formulas, and each one fits a different shape of problem. The table compares them so you can pick the simplest one that fits your rule.
| Approach | Use it when | Example result |
|---|---|---|
| IF alone | One test decides between two results | "Paid" or "Unpaid" |
| IF with AND or OR | Several tests combine into one yes-or-no result | "Pass" only when score and attendance both qualify |
| Nested IF or IFS | Three or more outcomes depend on the same value | Grades A through F |
Start with the simplest version that fits your rule. Add nesting only when a single test cannot describe all the results.
❓ FAQ
1. Can an IF formula compare text instead of numbers?
Yes. Put the text in quotes, such as =IF(C2="Active","Include","Skip"). Comparisons ignore capital letters, but extra spaces in the cell will stop a match, so trim the data first if results look wrong.
2. How do I count rows that meet several conditions?
IF returns a value for each row, so it does not add up results. To count rows that meet several conditions, use a counting function designed for that job, such as COUNTIFS, and then reference the result in a separate cell.
3. Can I leave the false result blank?
Yes. Use two quote marks with nothing between them as the false result, such as =IF(B2>=60,"Pass",""). The cell then looks empty when the test fails.
4. Can an IF formula use data from another sheet?
Yes. Include the sheet name before the cell reference, such as =IF(Sheet2!B2>=60,"Pass","Review"). Keep the sheet name simple and avoid spaces, or put it in single quotes if it has spaces.
📝 Summary
An Excel IF formula returns one result when a test is true and another when it is false. Combine several conditions with AND or OR, then check a boundary row and a blank row before you rely on the column.
