Logical Functions

Up to now, every formula we have written produces a single calculated result. Logical functions are different — they let Excel make decisions. Based on whether a condition is true or false, the formula takes one path or another. This is where spreadsheets start to feel genuinely intelligent. IF, AND, OR, IFS, and IFERROR are among the most used functions in all of Excel, and once you understand how they work you will find yourself reaching for them constantly.

The IF Function — Making a Decision

IF is the foundation of all logical functions. It evaluates a condition and returns one value if the condition is true and a different value if it is false. The structure is always the same three parts: the test, what to return if true, what to return if false.

=IF(logical_test, value_if_true, value_if_false)

=IF(B2>=100000, "Target Met", "Below Target")
=IF(A2="North", "Home Region", "Other")
=IF(C2>AVERAGE(C:C), "Above Average", "Below Average")

Formula in C2: =IF(B2>=100000, "Target Met", "Below Target")

Nested IF — Multiple Conditions in a Chain

What if you need more than two outcomes? You can place another IF inside the value_if_false argument, creating a chain of conditions. This is called a nested IF.

=IF(B2>=120000, "Excellent",
   IF(B2>=100000, "Good",
      IF(B2>=80000,  "Needs Work",
                     "Below Minimum")))

Nested IFs work well for up to three or four conditions. Beyond that they become hard to read and maintain — that is when you switch to IFS, which we will cover shortly. One important thing to remember: Excel evaluates the tests in order from top to bottom and stops at the first true condition. So always put your most exclusive condition first.

AND and OR — Testing Multiple Conditions at Once

The IF function tests one condition at a time. But what if your decision depends on two conditions both being true, or at least one of several conditions being true? That is where AND and OR come in — and they are almost always used inside an IF.

AND returns TRUE only if every condition you give it is true. If any one of them is false, AND returns FALSE.

OR returns TRUE if at least one of the conditions is true. It only returns FALSE if every single condition is false.

=AND(condition1, condition2, ...)
=OR(condition1, condition2, ...)

=IF(AND(B2>=100000, C2="North"), "Regional Star", "Standard")
  → "Regional Star" only if BOTH sales ≥ 100K AND region is North

=IF(OR(B2>=120000, C2="VIP"), "Priority Client", "Regular")
  → "Priority Client" if sales ≥ 120K OR client is VIP (either one)

NOT — Flipping a Condition

NOT simply reverses whatever a condition evaluates to. If the condition is TRUE, NOT makes it FALSE. If it is FALSE, NOT makes it TRUE. It is useful when it is easier to express what you do not want rather than what you do want.

=NOT(logical)

=IF(NOT(A2="Cancelled"), "Process Order", "Skip")
  → Processes the order for everything that is NOT Cancelled

=IF(NOT(ISBLANK(A2)), "Has value", "Empty")
  → Returns "Has value" if A2 is not blank

NOT pairs naturally with IS functions like ISBLANK, ISNUMBER, ISTEXT, and ISERROR. These check the type or state of a cell and return TRUE or FALSE — and wrapping them in NOT lets you flip the logic cleanly.

IFS — Multiple Conditions Without the Nesting

IFS is a cleaner alternative to nested IFs when you have many conditions. Instead of nesting IF inside IF inside IF, you list all your condition-result pairs in sequence. Excel tests each condition in order and returns the result for the first one that is true.

=IFS(condition1, result1, condition2, result2, ...)

Nested IF version (hard to read):
=IF(B2>=120000,"Excellent",IF(B2>=100000,"Good",IF(B2>=80000,"Needs Work","Below Min")))

IFS version (much cleaner):
=IFS(B2>=120000, "Excellent",
     B2>=100000, "Good",
     B2>=80000,  "Needs Work",
     TRUE,       "Below Min")

The final TRUE acts as the catch-all "else" — always add it.

Note the TRUE, "Below Min" at the end. Since TRUE is always true, this acts as the default case — the result returned when none of the earlier conditions matched. Always include it, otherwise IFS throws an error when no condition is met.

SWITCH — Matching Exact Values

Where IFS is great for range conditions (greater than, less than), SWITCH is cleaner when you are matching exact values. Think of it as a lookup table built into a formula — you give it a value to check, then list what to return for each possible match.

=SWITCH(expression, value1, result1, value2, result2, ..., [default])

=SWITCH(A2,
  "N", "North",
  "S", "South",
  "E", "East",
  "W", "West",
  "Unknown Region")    ← default if nothing matches

SWITCH is available in Excel 2019 and Microsoft 365. For older versions, a nested IF or a lookup table is the alternative.

IFERROR — Handling Formula Errors Gracefully

Nothing looks more unprofessional in a report than a column full of #N/A or #DIV/0! errors. IFERROR wraps any formula and specifies what to show instead if it produces an error — turning a messy error code into a clean zero, a dash, or a helpful message.

=IFERROR(formula, value_if_error)

=IFERROR(VLOOKUP(A2, Data, 2, 0), "Not Found")
  → Shows "Not Found" instead of #N/A when the lookup fails

=IFERROR(B2/C2, 0)
  → Shows 0 instead of #DIV/0! when C2 is empty or zero

=IFERROR(VALUE(A2), "")
  → Returns empty string instead of #VALUE! if A2 is not a number

COUNTIF and SUMIF — Conditional Counting and Totalling

These are not strictly logical functions but they belong here because they work by applying a condition — they are IF applied to counting and summing. They are used constantly in real data work.

COUNTIF counts how many cells in a range match a condition. SUMIF adds up the values in one range where the corresponding cells in another range match a condition.

=COUNTIF(range, criteria)
=SUMIF(range, criteria, [sum_range])

=COUNTIF(C2:C10, "North")            Count rows where region = North
=COUNTIF(B2:B10, ">=100000")         Count rows where sales ≥ 100,000
=SUMIF(C2:C10, "North", B2:B10)      Sum sales only for North region
=SUMIF(C2:C10, "North")              Sum values in C where region = North

For multiple conditions — like summing sales for the North region where sales also exceeded 100,000 — use COUNTIFS and SUMIFS, which accept as many condition pairs as you need.

=COUNTIFS(range1, criteria1, range2, criteria2, ...)
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)

=COUNTIFS(B2:B10, "North", C2:C10, ">=100000")
  Count rows where region=North AND sales≥100,000

=SUMIFS(C2:C10, B2:B10, "North", D2:D10, "Q1")
  Sum sales where region=North AND quarter=Q1

Next up — Math Functions, where you will learn SUMPRODUCT, MOD, INT, ABS, CEILING, FLOOR, and the functions that power financial and engineering calculations.