Excel IF Formula Examples: From One Condition to Nested Grade Bands

By the XLsheetAI Team · Updated August 7, 2026 · 9 min read

How the IF function works

IF asks a yes-or-no question about your data and returns one of two answers. The syntax is =IF(logical_test, value_if_true, value_if_false). The test is any comparison Excel can resolve to TRUE or FALSE. The other two arguments can be text in quotes, a number, a cell reference, or another formula.

Every example below uses the same small gradebook, so each formula builds on the last one.

The pass mark lives in G1 and holds the number 60. Keeping it in a cell rather than typing 60 into every formula pays off later.

Basic excel if formula examples

Start with a single comparison and a pair of text answers, then swap in numbers, then swap in a cell reference for the threshold. These three variations cover most day-to-day use. Each one is entered in E2 and filled down through E9 alongside the gradebook rows.

=IF(B2>=60, "Pass", "Fail")

Filled down, that returns Pass for Ana, Ben, Dan, Eve, Gia, and Hal, and Fail for Cara (55) and Finn (45). Note the quotes: text results always need them, and leaving them off is the single most common IF mistake.

Numbers work the same way but without quotes, which matters when you want to total the column afterwards:

=IF(B2>=60, 1, 0)

Ana returns 1, Cara returns 0, and =SUM(E2:E9) gives 6, the number of passing students. You can also return a calculation rather than a constant. A 10% bonus for scores of 90 or better looks like this:

=IF(B2>=90, B2*0.1, 0)

Ana scores 92, so she gets 9.2. Hal scores 88 and gets 0. Now replace the hard-coded threshold with the pass mark stored in G1, locked with dollar signs so it survives being filled down:

=IF(B2>=$G$1, "Pass", "Fail")

Change G1 from 60 to 65 and every result updates at once. Eve, on 61, flips from Pass to Fail. One edit instead of eight.

The comparison operators you can use

Excel supports six comparison operators inside a logical test, and any of them can drive an IF. They work on numbers, dates, and text. Text comparisons with = and <> ignore letter case, so "Yes" and "yes" are treated as the same value.

If you genuinely need case-sensitive matching, wrap the comparison in EXACT: =IF(EXACT(D2,"Yes"),"Received","Check spelling") returns Received only for a capital Y followed by lowercase es.

Nested IF for grade bands

One IF gives you two outcomes. Five grade bands need four tests, so you place each new IF inside the previous formula's false slot. Excel checks the tests in written order and stops at the first one that is true, which makes the order of your thresholds the whole ballgame.

=IF(B2>=90,"A",
  IF(B2>=80,"B",
    IF(B2>=70,"C",
      IF(B2>=60,"D","F"))))

On the gradebook that yields A for Ana (92), C for Ben (78), F for Cara (55), B for Dan (84), D for Eve (61), F for Finn (45), C for Gia (70), and B for Hal (88). The final "F" is the catch-all: it fires when every test above it has failed.

Now reverse the order and write =IF(B2>=60,"D",IF(B2>=70,"C",...)). Ana's 92 hits the first test, which is true, so she is graded D. Nothing errors out and nothing looks broken. This is the classic wrong-branch bug, and it is why descending thresholds are non-negotiable.

IFS: the cleaner modern alternative

IFS flattens the same logic into a list of condition and result pairs. There is no nesting and no closing-bracket pile-up at the end. It reads top to bottom in the order you write it and returns the first match. IFS needs Excel 2019 or Microsoft 365; Excel 2016 does not have it.

=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F")

Results match the nested version exactly: Ana A, Ben C, Cara F, Dan B, Eve D, Finn F, Gia C, Hal B. The final TRUE,"F" pair is the catch-all. Leave it out and any score below 60 returns #N/A, because IFS has no built-in default.

SWITCH is the third option, but it compares against exact values rather than ranges, so it suits status labels rather than numeric bands. =SWITCH(D2,"Yes","Received","No","Missing","Unknown") returns Received for Ana.

Stuck on which variation you need? Describe the rule in plain English, such as "give an A above 90, B above 80, otherwise fail", and XLsheetAI writes the correct IF, nested IF, or IFS for your version of Excel, ready to paste.

Combining IF with AND, OR and NOT

AND, OR, and NOT let a single IF weigh several conditions at once. AND returns TRUE only when every condition holds. OR returns TRUE when at least one does. NOT flips a TRUE to FALSE. All three go in the logical_test slot, and each accepts up to 255 conditions.

A student passes only if the score clears 60 and attendance clears 75:

=IF(AND(B2>=60, C2>=75), "Pass", "Review")

Ana, Ben, Dan, and Gia pass. Cara fails on both counts, Eve's attendance is 60, Finn's score is 45, and Hal's attendance is 55, so all four land in Review. Swap AND for OR and the logic loosens to "either one is enough":

=IF(OR(B2>=90, C2>=95), "Honor roll", "")

Ana qualifies on score, Gia qualifies on 98% attendance, and everyone else returns an empty string. NOT is useful when the negative case is the one you care about:

=IF(NOT(D2="Yes"), "Chase submission", "")

Cara and Eve get chased. That is identical in effect to =IF(D2<>"Yes","Chase submission",""), which most people find easier to read. Reach for NOT when wrapping something with no natural opposite operator, such as NOT(ISNUMBER(B2)).

IF with IFERROR, COUNTIF and SUMIF

IF gets far more useful when it takes input from other functions. IFERROR catches a broken lookup before it reaches your report, COUNTIF answers "how many rows match", and SUMIF answers "what do the matching rows add up to". Each one collapses into a single IF-driven answer.

Say F2 holds a student name typed by the user and you want their pass status. A misspelling would normally throw #N/A:

=IFERROR(IF(VLOOKUP($F$2,$A$2:$B$9,2,FALSE)>=60,"Pass","Fail"), "Not enrolled")

Type Dan and you get Pass. Type Ivy and you get Not enrolled instead of an error. Use IFERROR deliberately, though: it hides every error type, including a mistyped range you would rather find out about. XLOOKUP's fourth argument does the same job without that side effect, as covered in our guide to XLOOKUP vs VLOOKUP.

COUNTIF answers questions about the whole column and feeds the answer to IF:

=IF(COUNTIF($B$2:$B$9,">=90")>0, "Top scorer present", "None this term")

Only Ana clears 90, so the count is 1 and the formula returns Top scorer present. The same pattern checks membership: =IF(COUNTIF($A$2:$A$9,F2)=0,"Not enrolled","Enrolled").

SUMIF adds up only the rows that match a condition, which is handy for progress checks:

=IF(SUMIF($D$2:$D$9,"Yes",$B$2:$B$9)>400, "On track", "Behind")

The six students who submitted work total 457 points, so the result is On track. Note the quotes around the criteria: COUNTIF and SUMIF criteria are always text, even when they describe numbers.

Returning a blank, and why "" is not empty

Writing "" as a result makes a cell look empty, and it is the standard way to suppress noise in a report. But an empty text string is a value, not an absence. Excel treats the cell as occupied, which quietly changes how several other functions behave.

=IF(B2>=90, "Honor roll", "")

Fill that down and rows 3 through 9 look blank. Now test one of them. =ISBLANK(E3) returns FALSE, because the cell holds a formula result. =COUNTA(E2:E9) returns 8 rather than 1, because COUNTA counts anything that is not truly empty. COUNTBLANK, confusingly, does count those cells and returns 7.

The consequences are practical. Charts plot an empty string as zero instead of skipping the point, which puts a dip in your line, and Go To Special, Blanks will not find these cells at all.

Two fixes work. If a chart must skip the row, return =IF(B2>=90,"Honor roll",NA()), since charts ignore #N/A points. For clean static data, copy the column, Paste Special as Values, then use Go To Special, Blanks to clear the leftovers.

Driving conditional formatting with an IF-style rule

Conditional formatting uses the same logical tests as IF, but without the IF wrapper. You supply only the condition, and Excel applies your format wherever it evaluates to TRUE. The formatting is the true branch; leaving the cell alone is the false branch.

To shade every failing student's whole row, select A2:D9, open Home, Conditional Formatting, New Rule, choose "Use a formula to determine which cells to format", and enter:

=$B2<60

The single dollar sign locks the column to B while letting the row number move, so the rule reads row 2's score for row 2, row 3's score for row 3, and so on. Cara and Finn get highlighted across all four columns. Drop the dollar sign and each cell is compared against its own column, which produces nonsense.

Multi-condition rules use the same syntax as the IF versions above. =AND($B2>=60,$C2<75) flags students who passed but attended poorly, which here is Hal alone.

When to stop nesting

Modern Excel permits 64 levels of nested IF, but readability collapses long before that. Three levels is a comfortable ceiling, four is pushing it, and anything deeper is a maintenance problem waiting to happen. At that point the right move is a different tool, not a longer formula.

The warning signs are consistent: you start counting closing brackets, you cannot tell which threshold produced a given result, and changing one band means retyping the middle of a formula.

If you are on Excel 2019 or 365, IFS solves most of this. If your bands are numeric and might change, a lookup table is better still. Put the band floors in F2:F6 (0, 60, 70, 80, 90) and the grades in G2:G6 (F, D, C, B, A), sorted ascending, then use approximate match:

=VLOOKUP(B2, $F$2:$G$6, 2, TRUE)

Ben's 78 falls between 70 and 80, so VLOOKUP steps back to the 70 row and returns C. The XLOOKUP equivalent is =XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6,,-1), where -1 means "next smaller item". Either way the bands now live in visible cells anyone can edit without touching a formula, and the approach scales to twenty bands as easily as five. For more patterns worth keeping close, see our Excel formulas cheat sheet.

Common IF errors and how to fix them

Most IF failures come from five causes: a type mismatch inside the calculation, missing quotes around text, thresholds tested in the wrong order, an assumption that text comparison respects capitalisation, and numbers that Excel is storing as text. The table below pairs each with its fix.

One habit prevents most of these: after writing an IF, deliberately test a value that should hit the false branch. Formulas checked only against the true case ship broken far more often.

Bottom line

IF is three arguments and one rule: test, true result, false result. Nest it for two or three bands, quote your text, order thresholds from high to low, and keep them in cells so a policy change is one edit. Past three levels, move to IFS or a lookup table. Combine IF with AND, OR, IFERROR, COUNTIF, and SUMIF to keep the logic in one readable cell rather than a chain of helper columns.

Learn by doing. XLsheetAI turns plain-English rules into working IF, IFS, and nested formulas, and explains any formula you paste in step by step, so you can see exactly which branch fires and why.