📊 Lesson 4.3: Conditional Logic — IFS, AND/OR, IFERROR & the *IF(S) Family

Nested IF, and Why IFS Is Cleaner

A single IF handles a two-way choice: =IF(B2>=60, "Pass", "Fail") reads "if the score in B2 is at least 60, say Pass, otherwise Fail." That's perfect when there are exactly two outcomes. But life often has more than two — a grade might be A, B, C, D, or F; an order might be On track, Due soon, or Overdue. The old way to handle that was to nest one IF inside another, each one handling the next case in the "otherwise" slot:

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

It works, and you'll see it in older workbooks, but read it again — it's a thicket. Every extra grade adds another IF( and another closing ) at the far end, and a single misplaced parenthesis breaks the whole thing. Nested IFs are hard to write, harder to read, and genuinely painful to edit six months later.

IFS — one function, many conditions

IFS was built exactly to untangle this. You give it a flat list of condition, result pairs, and it returns the result for the first condition that's true — checked left to right, top to bottom. No nesting, no pile of closing parentheses:

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

Read it as a simple table of rules: "90 or more → A; else 80 or more → B; else 70 → C; else 60 → D; otherwise → F." Two things to note. First, order matters — because IFS stops at the first true condition, you go from strictest to loosest (highest score first). Second, that final TRUE,"F" is the catch-all: TRUE is always true, so it acts as the "none of the above" default. Without a catch-all, a value matching no condition returns an #N/A error.

AND, OR & NOT — Combining Conditions

So far each test has been a single comparison. But real rules often depend on several things at once: "flag it only if the order is late and unpaid," or "highlight it if the customer is VIP or the total is large." That's what AND, OR, and NOT are for. Each takes a list of conditions and returns a single TRUE or FALSE, which you drop straight into an IF:

On their own they just return true or false, so you almost always nest them inside an IF to turn that verdict into something useful. For example, to mark an order that is both overdue and unpaid:

=IF(AND(D2<TODAY(), E2<>"Paid"), "⚠️ Chase", "OK")

Read it as: "if the due date D2 is before today and the status E2 is not 'Paid', say Chase; otherwise OK." (<> means "not equal to.") You can nest these freely — an OR inside an AND, an IFS whose conditions are ANDs — to express quite sophisticated rules while keeping each piece readable.

IFERROR & IFNA — Catching Errors Gracefully

Formulas fail sometimes — a lookup finds nothing, a division hits a zero, a value is the wrong type. When they do, Excel shows a blunt error code like #N/A, #DIV/0!, or #VALUE!. Those are useful while you're debugging, but ugly in a finished report and they spread: one error cell can poison a SUM that includes it. IFERROR lets you catch any error and show something friendly instead:

=IFERROR(A2/B2, 0)

This says "calculate A2 divided by B2; but if that produces any error, show 0 instead." You can return anything — a 0, an empty string "", or a message like "Check input". It pairs perfectly with the lookups from Lessons 4.1–4.2: =IFERROR(VLOOKUP(F2,$A$2:$D$5,4,FALSE), "Not found") turns a missing code from a scary #N/A into a calm "Not found."

IFNA — catch only the "not found" error

IFERROR is a broad net: it swallows every kind of error, which can hide a genuine bug (a typo'd formula) behind a friendly message. When you specifically want to handle a lookup miss and still see other errors, use IFNA, which catches only #N/A:

=IFNA(XLOOKUP(F2,$A$2:$A$5,$D$2:$D$5), "Not found")

Here a genuine #N/A (code not in the list) shows "Not found," but if the formula had a different error you'd still see it and know to fix it. As a rule: reach for IFNA around lookups, and IFERROR when you truly want to mop up any error at all.

The Conditional-Aggregate Family

Here's where logic meets analysis. You already know SUM, COUNT, and AVERAGE from Lesson 2.2 — they crunch a whole range. The conditional versions do the same, but only for rows that meet a criterion: total the sales where the region is West, count the orders where the status is Overdue, average the scores where the score is above 80. This single family answers most "how much / how many, by category" questions you'll ever ask — and it's the engine behind a summary table.

Each comes in a single-condition flavor and a plural -IFS flavor for multiple conditions:

Writing Criteria — Text, Numbers, Comparisons & Wildcards

The heart of every *IF(S) function is the criteria — the little test each row must pass. Criteria are more flexible than they first appear, and getting them right is most of the skill:

• Text — put it in quotes: "West", "Overdue". Matching is not case-sensitive, so "west" matches "West".
• Numbers — just the number, no quotes needed for a plain equal: 100 counts rows equal to 100.
• Comparisons — wrap the operator and value in quotes as one string: ">100" (greater than 100), "<=50" (50 or less), "<>0" (not zero). This surprises people — the comparison lives inside the quotes.
• A cell reference — point at a cell so the criterion is live: if F1 holds the region, =SUMIF(C2:C50, F1, D2:D50) retotals the moment you change F1. For a comparison against a cell, join with &: ">"&F1.
• Wildcards — * matches any run of characters and ? matches a single character. "North*" matches North, Northeast, Northwest; "?at" matches cat, hat, bat. Great for grouping messy text.

Put these together and you can answer remarkably specific questions in one formula: "How many West-region orders over $500 are still unpaid?" becomes =COUNTIFS(C2:C50,"West", D2:D50,">500", E2:E50,"<>Paid"). Every extra test_range, criteria pair is another and narrowing the result. This is the exact machinery that a PivotTable (Lesson 5.1) automates with a friendly interface — but writing it by hand teaches you what's really happening underneath.

🎯 Project: Status Flags & Category Summaries

Now make your tracker think. You'll add a status column driven by IFS, then build a little summary block beside it that totals and counts by category with SUMIFS and COUNTIFS. Together they turn your raw list into something that reads like a report.

Status flag from a days-remaining value in D2:
=IFS(D2<0,"Overdue", D2<=3,"Due soon", TRUE,"On track")

Combined-condition flag (due date in its own column, F2, and
payment status in G2):
=IF(AND(F2<TODAY(), G2<>"Paid"), "⚠️ Chase", "OK")

Category summary (categories listed down column H, data in B and C):
Total for this category:  =SUMIFS($C$2:$C$50, $B$2:$B$50, H2)
Count for this category:  =COUNTIFS($B$2:$B$50, H2)
Average for this category: =AVERAGEIFS($C$2:$C$50, $B$2:$B$50, H2)

Safe division that never shows #DIV/0!:
=IFERROR(C2/B2, 0)

Best Practices for Conditional Logic

✅ Do's

• Reach for IFS over nested IF once you have three or more outcomes, and always add a TRUE catch-all.
• Say the rule in plain English first — "if late and unpaid" — then translate it to IF/AND/OR.
• Reference criteria from cells (a drop-down or a summary label) so your logic is live and easy to change.
• Use IFNA around lookups so you catch genuine misses without hiding other bugs.

❌ Don'ts

• Don't blanket-wrap everything in IFERROR. That hides real mistakes behind a friendly mask.
• Don't forget the comparison goes inside quotes — it's ">100", not >100.
• Don't mix up the argument order — SUMIF puts the sum range last, SUMIFS puts it first.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

Your spreadsheet now makes decisions and summarizes itself. You can classify every row with IFS, express multi-part rules with AND/OR, keep errors from spoiling a report, and answer "how much / how many, by category" with the conditional-aggregate family. That's the logic layer of a dashboard — and it's exactly the reasoning a PivotTable will soon automate for you.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 4.4: Dynamic Arrays — FILTER, SORT & UNIQUE (Excel's Modern Power) — we meet formulas that spill whole lists of results from a single cell. You'll pull matching rows with FILTER, order them with SORT, and distill unique values with UNIQUE — modern superpowers that make live summaries almost effortless.

✅ Before the Next Lesson

• Confirm your status flags and category summary update when you edit the data
• Try one criterion with a comparison (">100") and one with a wildcard ("North*")
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Microsoft Excel Help & Learning (Microsoft Support)
• microsoft365.com — open Excel for the web
• Microsoft Excel — product overview