Session 12 · Phase 2: Excel

Excel Conditional & Logical Functions — IF, SUMIF, COUNTIF

Make Excel decide — classify values with IF, and sum/count/average only the rows that meet your conditions with the *IF family.

⏱ ~2 hrs 📚 Core content 🎯 High priority

Learning Objectives

Use IF and IFS for conditional logic, and avoid deeply nested IFs.
Combine conditions with AND, OR, and NOT.
Sum with SUMIF / SUMIFS and count with COUNTIF / COUNTIFS.
Average with AVERAGEIF / AVERAGEIFS.
Explain the difference between COUNT, COUNTA, COUNTBLANK, and COUNTIF.

0. The Sample Data

A small sales table used throughout — column A is region, B is product, C is sales amount.

	A (region)	B (product)	C (sales)
2	North	Pen	100
3	South	Pen	150
4	North	Notebook	200
5	East	Marker	50
6	South	Notebook	300
7	North	Marker	75

1. IF and IFS — Conditional Logic

IF — one condition, two outcomes

=IF(logical_test, value_if_true, value_if_false)

=IF(C2 >= 100, "High", "Low")

If the sales value in C2 is 100 or more, return "High"; otherwise "Low".

IFS — multiple conditions, cleaner than nested IFs

=IFS(condition1, value1, condition2, value2, ..., TRUE, default)

=IFS(C2 >= 200, "High", C2 >= 100, "Medium", TRUE, "Low")

💡

Pro Tip: Deeply nested IFs (=IF(... IF(... IF(...)))) get unreadable fast. Use IFS for multiple conditions — it's cleaner and the intent is obvious.

2. AND, OR, and NOT

Combine multiple conditions inside an IF (or any logical test).

-- TRUE only if BOTH conditions hold
=IF(AND(A2="North", C2>=100), "Target hit", "Miss")

-- TRUE if EITHER condition holds
=IF(OR(A2="North", A2="South"), "In scope", "Out")

-- Reverses a condition
=IF(NOT(A2="North"), "Not North", "North")

3. SUMIF and SUMIFS — Conditional Sums

SUMIF — one condition

=SUMIF(range, criteria, [sum_range])

-- Sum of sales where region is "North"
=SUMIF(A2:A7, "North", C2:C7)   →  375  (100 + 200 + 75)

SUMIFS — multiple conditions (note the argument order!)

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

-- Sum of sales where region is North AND product is Pen
=SUMIFS(C2:C7, A2:A7, "North", B2:B7, "Pen")   →  100

⚠️

Common mistake: SUMIF puts the sum range last; SUMIFS puts the sum range first. Mixing these up is a frequent (and easy-to-catch) interview error.

4. COUNTIF and COUNTIFS — Conditional Counts

COUNTIF — one condition

=COUNTIF(range, criteria)

-- Number of North rows
=COUNTIF(A2:A7, "North")   →  3

COUNTIFS — multiple conditions

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

-- North rows with sales >= 100
=COUNTIFS(A2:A7, "North", C2:C7, ">=100")   →  2  (Pen 100, Notebook 200)

5. AVERAGEIF and AVERAGEIFS

Same pattern as SUMIF/COUNTIF, but returns an average.

-- Average sales in the South region
=AVERAGEIF(A2:A7, "South", C2:C7)   →  225  (avg of 150 and 300)

-- Average sales in the South, for the Notebook product
=AVERAGEIFS(C2:C7, A2:A7, "South", B2:B7, "Notebook")   →  300

6. COUNT vs COUNTA vs COUNTBLANK vs COUNTIF

This is one of the most-asked Excel interview questions. The four count functions differ by what they count.

Function	What it counts
`COUNT`	only numeric cells
`COUNTA`	all non-empty cells (numbers + text)
`COUNTBLANK`	only empty cells
`COUNTIF`	cells that meet a condition

=COUNT(A1:A10)        -- counts only numbers
=COUNTA(A1:A10)       -- counts anything non-blank
=COUNTBLANK(A1:A10)   -- counts blanks (useful for finding missing data)
=COUNTIF(A1:A10, ">100")  -- counts cells over 100

📝

Real-world use: COUNTBLANK is great for auditing missing data (e.g. how many customers are missing an email address).

📋 Stable content — Reviewed: August 2026

7. Interview Questions (with Model Answers)

The conditional/logical function questions interviewers ask. Self-test before revealing.

IQ1. What is the IF function, and how does it work?

Model answer: "IF performs a logical test and returns one value if it's true and another if it's false — =IF(condition, value_if_true, value_if_false). I use it to classify values, like labeling a sale as High or Low."

IQ2. What's the difference between IF and IFS?

Model answer: "IF handles a single condition; IFS handles multiple conditions without nesting. IFS checks each condition in order and returns the first match. It's cleaner than a long nested IF."

IQ3. What's the difference between SUMIF and SUMIFS?

Model answer: "SUMIF sums with a single condition; SUMIFS sums with multiple conditions. Also, SUMIF puts the sum range last, while SUMIFS puts it first — a common source of mistakes."

IQ4. What's the difference between COUNTIF and COUNTIFS?

Model answer: "COUNTIF counts cells matching one condition; COUNTIFS counts cells matching multiple conditions across multiple ranges. They follow the same single-vs-multiple pattern as SUMIF/SUMIFS."

IQ5. Explain the difference between COUNT, COUNTA, COUNTBLANK, and COUNTIF.

Model answer: "COUNT counts only numeric cells. COUNTA counts all non-empty cells, text included. COUNTBLANK counts empty cells. COUNTIF counts cells that meet a specific condition."

IQ6. When would you use AND or OR inside an IF?

Model answer: "When I need multiple conditions in one test — AND when all must be true, OR when any one is enough. For example, =IF(AND(region="North", sales>=100), "Target", "Miss")."

IQ7. How do you avoid writing deeply nested IF statements?

Model answer: "I use IFS for multiple conditions, or better, use a lookup table with VLOOKUP/XLOOKUP to map values to categories. Both are far more readable than a long nested IF."

IQ8. How would you sum sales where region is 'North' AND product is 'Pen'?

Model answer: "I'd use SUMIFS with the sum range first: =SUMIFS(C2:C7, A2:A7, "North", B2:B7, "Pen")."

IQ9. How would you count how many customers are missing an email address?

Model answer: "I'd use COUNTBLANK on the email column — it counts empty cells, which tells me exactly how many customers have no email on file."

Hands-On Project: Conditional Analysis

Using the sales table (region, product, sales), write formulas for each task.

Steps

Add a column that labels each sale as "High" (≥200) or "Low" (<200) using IF.
Sum the sales for the "South" region using SUMIF.
Count the number of "North" rows using COUNTIF.
Sum sales where region is "North" AND product is "Notebook" using SUMIFS.
Find the average sales for the "South" region using AVERAGEIF.

View Solution / Walkthrough

-- 1. High/Low label
=IF(C2 >= 200, "High", "Low")

-- 2. South total
=SUMIF(A2:A7, "South", C2:C7)   →  450  (150 + 300)

-- 3. North count
=COUNTIF(A2:A7, "North")        →  3

-- 4. North + Notebook
=SUMIFS(C2:C7, A2:A7, "North", B2:B7, "Notebook")   →  200

-- 5. South average
=AVERAGEIF(A2:A7, "South", C2:C7)   →  225

Key Takeaways

IF returns one of two values; IFS handles multiple conditions cleanly.

SUMIF = single condition; SUMIFS = multiple (and the sum range moves to first).

COUNTIF/COUNTIFS and AVERAGEIF/AVERAGEIFS follow the same pattern.

COUNT = numbers only; COUNTA = non-blank; COUNTBLANK = blanks; COUNTIF = conditional.

Use AND/OR/NOT to combine conditions inside a logical test.

Learning Objectives

0. The Sample Data

1. IF and IFS — Conditional Logic

IF — one condition, two outcomes

IFS — multiple conditions, cleaner than nested IFs

2. AND, OR, and NOT

3. SUMIF and SUMIFS — Conditional Sums

SUMIF — one condition

SUMIFS — multiple conditions (note the argument order!)

4. COUNTIF and COUNTIFS — Conditional Counts

COUNTIF — one condition

COUNTIFS — multiple conditions

5. AVERAGEIF and AVERAGEIFS

6. COUNT vs COUNTA vs COUNTBLANK vs COUNTIF

7. Interview Questions (with Model Answers)

Hands-On Project: Conditional Analysis

Steps

Key Takeaways

Objective Questions — Test Your Understanding