# Logical Functions

Course: [Excel](https://www.devtutorials.ai/learn/excel) · Section: Formulas and Functions
Source: https://www.devtutorials.ai/learn/excel/formulas-and-functions/logical-functions

> Make decisions with IF, nested IFs, AND, OR, and NOT, and summarize with COUNTIF and SUMIF. Then build a commission sheet in the Chapter 4 project.

So far, your formulas have performed calculations. Now they can make decisions. Logical functions allow Excel to evaluate conditions and return different results depending on whether those conditions are TRUE or FALSE. In this lesson, you'll learn how to use **IF**, **AND**, **OR**, and **NOT**, create multiple outcomes with nested IF statements, and summarize data using **COUNTIF** and **SUMIF**.

Open the **Excel sandbox** and follow along.

## Comparison operators

Conditions use **comparison operators**. Each comparison is either **TRUE** or **FALSE**.

| Operator | Meaning | Example (B2 = 60) |
|---|---|---|
| `=` | Equal to | `B2=60` → TRUE |
| `<>` | Not equal to | `B2<>60` → FALSE |
| `>` | Greater than | `B2>50` → TRUE |
| `<` | Less than | `B2<50` → FALSE |
| `>=` | Greater than or equal to | `B2>=60` → TRUE |
| `<=` | Less than or equal to | `B2<=59` → FALSE |

## IF()

**IF** checks a condition and returns one value if it's TRUE and another if it's FALSE:

```text title="IF has three arguments"
=IF(condition, value_if_true, value_if_false)
=IF(B2>=50, "Pass", "Fail")
```

If **B2** is 50 or more, the cell shows **Pass**; otherwise it shows **Fail**.

- Put **text** results in double quotes: `"Pass"`.
- Results can also be numbers or calculations: `=IF(B2>1000, B2*10%, 0)` gives a 10% bonus on sales over 1000, and 0 otherwise.

## Multiple conditions

For more than two outcomes, put one IF inside another. This is called a **nested IF**:

```text title="A nested IF gives three outcomes"
=IF(B2>=80, "Distinction", IF(B2>=50, "Pass", "Fail"))
```

Excel checks the first condition. If it's FALSE, it moves on to the second IF.

Check from the **highest** threshold down: 80 first, then 50. If you checked `>=50` first, an 85 would stop there and get "Pass" instead of "Distinction".

## AND()

**AND** is TRUE only when **all** its conditions are TRUE.

`=IF(AND(B2>=50, C2>=50), "Pass", "Fail")` passes only if **both** scores are 50 or more.

## OR()

**OR** is TRUE when **at least one** of its conditions is TRUE.

`=IF(OR(B2="Manager", C2>5), "Eligible", "Not eligible")` is eligible if the person is a manager **or** has more than 5 years' service.

## NOT()

**NOT** flips TRUE to FALSE and FALSE to TRUE.

`=IF(NOT(B2="Paid"), "Send reminder", "")` reminds everyone whose status isn't "Paid". (`""` shows nothing.)

Text comparisons in IF, COUNTIF, and SUMIF ignore capital letters: `"paid"` matches `Paid`. But spelling and extra spaces matter, so `Paid ` with a trailing space won't match. Drop-down lists (Lesson 19) keep entries consistent.

## COUNTIF() and SUMIF()

These two count and add **only the cells that meet a condition**:

```text title="Counting and adding with a condition"
=COUNTIF(range, condition)
=COUNTIF(C2:C20, "Pass")         how many passed
=COUNTIF(B2:B20, ">=80")         how many scored 80 or more

=SUMIF(range, condition, sum_range)
=SUMIF(A2:A20, "Food", B2:B20)   total spent on Food
```

Comparisons in COUNTIF and SUMIF go **inside quotes**: `">=80"`.

**IF** decides for one row. **COUNTIF** and **SUMIF** summarize many rows. Together they answer most everyday questions: "who passed?", "how many passed?", and "how much did we spend on food?"

## Common mistakes

| Mistake | What happens | How to fix it |
|---|---|---|
| Missing quotes around text | `#NAME?` error | Add quotes: `"Pass"` |
| Using `=>` instead of `>=` | Excel rejects the formula | Write `>=` and `<=` with `=` second |
| Nested IF in the wrong order | High scores get the lower grade | Test the highest threshold first |
| Parentheses don't match | Excel warns about the formula | Count `(` and `)`, one pair per function |
| COUNTIF condition without quotes | `=COUNTIF(B2:B9, >=80)` is rejected | Write `">=80"` |

## Exercise

Click **Reset** in the sandbox header. Type:

- **A1**: `Name`, **B1**: `Score`, **C1**: `Result`
- **A2** to **A5**: `Ana`, `Ben`, `Cara`, `Dev`
- **B2** to **B5**: `45`, `68`, `82`, `91`

Then do each task and press **Check**.

## Chapter project

Put Chapter 4 together. You'll build a sales commission sheet with formulas, an absolute reference, functions, and IF decisions.

## What to remember

- Comparisons (`=`, `<>`, `>`, `<`, `>=`, `<=`) are TRUE or FALSE.
- `=IF(condition, if_true, if_false)` makes a decision; text results go in quotes.
- Nested IFs give more than two outcomes; test the highest threshold first.
- **AND** needs all conditions TRUE; **OR** needs one; **NOT** flips the result.
- **COUNTIF** counts and **SUMIF** adds only the rows that meet a condition.

That's the end of **Chapter 4**. In **Chapter 5**, you'll learn how to find, sort, filter, and validate data.