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

Last lesson you pulled a single value out of another table. Now we go one step bigger: instead of asking "what's the value for this one key," you'll ask "what's the total for everything that matches these rules." That's conditional aggregation — summing, counting, and averaging a filtered subset of your data — and it's the engine behind almost every summary panel and dashboard you'll ever build.

📚 What You'll Learn

By the end of this lesson, you will be able to:

Aggregate a subset of your data with a condition instead of totalling everything
Use SUMIF, COUNTIF, and AVERAGEIF with text, number, wildcard, and comparison criteria
Step up to the plural SUMIFS, COUNTIFS, AVERAGEIFS for multiple conditions — and dodge the argument-order gotcha
Combine logic with AND, OR, NOT, and replace nested IFs with clean IFS (and know when SWITCH fits)
Keep dashboards tidy with IFERROR and IFNA — and recognize when hiding an error is the wrong call

⏱️ Estimated Time: 55 minutes

🎯 Project: Build a summary panel that answers real questions from a sales dataset — total sales for one region (SUMIF), count of tasks that are Done (COUNTIF), an average across multiple conditions (AVERAGEIFS), a tidy status label with IFS, all made robust with IFERROR.

In This Lesson

From One Row to a Whole Subset

Plain SUM adds up everything in a range. But real questions are rarely "add up everything." They're "add up the sales from the West region," or "count the tasks that are Done," or "average the order values over 100 dollars." Each of these has a condition — a rule that decides which rows count and which get skipped.

Conditional aggregation is exactly this: you give a function a criterion, it walks down your data, keeps only the rows that match, and then sums, counts, or averages just those. It's like handing an assistant a stack of receipts and saying "add up only the ones from the grocery store." Same pile, but a targeted answer.

🧠 Mindset

Every conditional aggregation is two ideas glued together: a filter (which rows qualify) and a math operation (what to do with them). Once you see functions this way, the whole family stops being a list to memorize and becomes one simple pattern: "pick the rows that match, then do the math." SUMIF sums them, COUNTIF counts them, AVERAGEIF averages them — the filter idea is identical.

We'll use this little sales dataset throughout the lesson. Imagine it lives in A1:D6:

A (Region)	B (Rep)	C (Amount)	D (Status)
West	Ana	120	Done
East	Ben	80	Open
West	Ana	200	Done
East	Cara	150	Done
West	Ben	60	Open

graph TD A["📋 Full dataset
every sales row"] --> B["🔍 Apply criteria
Region is West and Amount over 100"] B --> C["✅ Matching subset
only the rows that qualify"] C --> D["🧮 Aggregated answer
sum · count · average"]

SUMIF, COUNTIF & AVERAGEIF

These three singular functions each take one condition. Learn the shape once and all three follow.

📖 Definition

=SUMIF(range, criterion, [sum_range])
=COUNTIF(range, criterion)
=AVERAGEIF(range, criterion, [average_range])

range — the column to test against the criterion.
criterion — the rule a cell must satisfy to count.
sum_range / average_range — the column to actually add or average, when it's different from the range you tested. COUNTIF needs no such argument — it just counts matches.

Worked examples on our dataset

Total sales for the West region: test column A for "West", but add up column C.

=SUMIF(A2:A6, "West", C2:C6)

That keeps the West rows (120, 200, 60) and adds them to 380. Notice the test range (A2:A6) and the sum range (C2:C6) are different columns — that's what the third argument is for.

Count of tasks that are Done: COUNTIF just counts matches in one column.

=COUNTIF(D2:D6, "Done")

Three rows say "Done", so it returns 3.

Average amount for Ana:

=AVERAGEIF(B2:B6, "Ana", C2:C6)

Ana's rows are 120 and 200, averaging 160.

Criteria syntax — text, numbers, comparisons, wildcards

The criterion is more flexible than it first looks:

You want…	Write the criterion as…	Example
Exact text	Text in quotes	`"West"`
A number	The number, quotes optional	`100`
Greater than a value	Comparison string in quotes	`">100"`
Greater than or equal	Comparison string	`">=100"`
Not equal to	Comparison string	`"<>Done"`
Starts with "St"	Wildcard `*` (any characters)	`"St*"`
Any single character	Wildcard `?`	`"A?a"`

So total of all sales over 100 is:

=SUMIF(C2:C6, ">100", C2:C6)

Here the test range and sum range are the same column, which is fine — we're testing amounts and summing amounts. It keeps 120, 200, 150 and returns 470.

✅ Pro Tip — compare against a cell, not a hard-coded number

To make the threshold live, put it in a cell (say F1) and build the criterion by joining the operator to the cell with &:

=SUMIF(C2:C6, ">"&F1, C2:C6)

Now change F1 and the total re-filters instantly. That little ">"&F1 pattern — an operator string joined to a cell reference — is one of the most useful tricks in the whole family.

⚠️ Important Note: The comparison and wildcard forms live inside quotes as text — ">100", not >100. Beginners often write the operator outside the quotes and get an error. If a criterion misbehaves, the first thing to check is whether the whole condition, operator included, is wrapped in one pair of quotes.

The Plural *IFS — Multiple Criteria

The singular functions test one condition. Real questions often stack several: "total sales that are West and Done," or "count of West orders over 100." That's where the plural forms come in: SUMIFS, COUNTIFS, AVERAGEIFS. Each accepts as many criteria pairs as you like, and a row must satisfy all of them to count.

⚠️ Watch Out — the argument order is different! (the big gotcha)

This trips up nearly everyone. In the singular SUMIF, the range to sum comes last. In the plural SUMIFS, the sum range comes first, before any criteria:

Singular:  =SUMIF(test_range, criterion, sum_range)
Plural:    =SUMIFS(sum_range, test_range1, criterion1, test_range2, criterion2, ...)

Memorize it: SUMIFS puts the thing you're adding up first. COUNTIFS is the exception that makes sense — it has nothing to add up, so it's just pairs of range and criterion.

Worked examples

Total West sales that are Done: sum column C where region is West AND status is Done.

=SUMIFS(C2:C6, A2:A6, "West", D2:D6, "Done")

West-and-Done rows are 120 and 200, so it returns 320.

Count of West orders over 100:

=COUNTIFS(A2:A6, "West", C2:C6, ">100")

West rows over 100 are 120 and 200 — a count of 2.

Average amount for East, Done only:

=AVERAGEIFS(C2:C6, A2:A6, "East", D2:D6, "Done")

The only East-and-Done row is Cara's 150, so the average is 150.

💡 All ranges must be the same size and shape

Every criteria range and the sum/average range must cover the same rows — same start and end. If one range is A2:A6 and another is C2:C7, you'll get a #VALUE! error because the function can't line the rows up. Keep them all aligned to the same row span.

Logical Helpers: AND, OR, NOT, IFS

Alongside the aggregation family, a handful of pure-logic functions help you build the rules themselves — and label results cleanly.

AND, OR, NOT — combining true/false tests

AND(…) is TRUE only when every condition inside is true.
OR(…) is TRUE when at least one condition is true.
NOT(…) flips a single true/false result.

They shine inside an IF. For instance, "flag a row as a Priority if it's West and over 100":

=IF(AND(A2="West", C2>100), "Priority", "Normal")

And "flag as Follow-up if it's Open or under 100":

=IF(OR(D2="Open", C2<100), "Follow-up", "OK")

IFS — a clean alternative to nested IF

When you have several tiers to label, nesting IF inside IF inside IF gets ugly and error-prone fast — a wall of parentheses nobody can read. IFS lets you list condition/result pairs in order, and it returns the result of the first condition that's true:

=IFS(C2>=200, "Large", C2>=100, "Medium", C2<100, "Small")

Read top to bottom: "If 200 or more, Large; otherwise if 100 or more, Medium; otherwise Small." Far easier to read and edit than the nested-IF equivalent.

⚠️ Watch Out — give IFS a catch-all

IFS returns #N/A if none of its conditions is true. To avoid that, make the last condition a catch-all that's always true — literally TRUE:

=IFS(C2>=200, "Large", C2>=100, "Medium", TRUE, "Small")

That final TRUE acts like an "otherwise" and guarantees every row gets a label.

💡 SWITCH — when you're matching one value against a list

If you're checking a single cell against a set of exact values (not ranges), SWITCH reads more cleanly than IFS: =SWITCH(A2, "West", 1, "East", 2, 0) returns 1 for West, 2 for East, and 0 for anything else. Use IFS for comparisons and ranges; reach for SWITCH when you're mapping exact values to results.

Error Handling for Clean Dashboards

A summary panel that shows #DIV/0! or #N/A looks broken, even when the formula is behaving reasonably. For example, AVERAGEIF returns #DIV/0! when no rows match — because it tried to divide by zero matches. That's a normal, expected situation you may want to dress up.

IFERROR — catch any error, show a fallback

=IFERROR(AVERAGEIF(A2:A6, "South", C2:C6), "No data")

There are no South rows, so the average would error; IFERROR replaces it with the friendlier "No data". IFERROR catches every error type.

IFNA — catch only #N/A

As we saw with lookups in Lessons 4.1 and 4.2, IFNA is the narrow tool: it catches only the "no available value" #N/A error and leaves everything else visible. It pairs especially well with IFS and lookups where #N/A is the expected "nothing matched" signal.

⚠️ When hiding an error is wrong

Error-wrapping is for expected, harmless situations — no rows in a region yet, an empty input cell. It is not a way to make a genuinely broken formula look fine. If you wrap a #DIV/0! that's actually caused by a bug in your logic, you've hidden the symptom and kept the disease. Worse, a "No data" that should say "247" can send someone into a bad decision. Fix the cause first; dress up the display only for cases you understand and accept.

⚠️ Important Note: A good habit: build the formula without the error wrapper first and confirm it returns what you expect. Only once it's correct do you wrap it, so the wrapper is hiding an expected edge case rather than masking a mistake you never noticed.

🎯 Project: A Summary Panel

Now build a real summary panel over a sales dataset — the kind of "answers at a glance" block that sits atop most dashboards. You'll answer four genuine questions with four different functions and make each one robust.

🏋️ Build a conditional summary panel

Objective: Given a sales table, build a small panel that reports a region total, a completed-task count, a multi-condition average, and a clean per-row status label — all resilient to empty or missing cases.

Instructions (about 25 minutes):

(5 min) In a new sheet, enter the dataset in A1:D6. Headers in row 1: Region, Rep, Amount, Status. Then five rows: West/Ana/120/Done, East/Ben/80/Open, West/Ana/200/Done, East/Cara/150/Done, West/Ben/60/Open.
(4 min) In a summary area (labels in column F starting at F2, results beside them in column G — keep F1 free for a threshold), label and compute Total West sales with SUMIF.
(4 min) Add Count of Done tasks with COUNTIF.
(4 min) Add Average amount for East, Done only with AVERAGEIFS — mind the argument order (sum/average range first!).
(4 min) In a new column beside the data, add a size label for each row with IFS (Large / Medium / Small), including a TRUE catch-all.
(2 min) Wrap your AVERAGEIFS (or add a South-region average) in IFERROR so an empty result shows "No data" rather than #DIV/0!.
(2 min) Change a couple of amounts and a status and watch every summary number update live.

💡 Hint — starter formulas

Dataset A1:D6
A: Region  B: Rep  C: Amount  D: Status

Summary panel:
Total West sales:
=SUMIF(A2:A6, "West", C2:C6)

Count of Done tasks:
=COUNTIF(D2:D6, "Done")

Average for East, Done only (note: sum range FIRST):
=AVERAGEIFS(C2:C6, A2:A6, "East", D2:D6, "Done")

Per-row size label (put in E2, drag down):
=IFS(C2>=200, "Large", C2>=100, "Medium", TRUE, "Small")

Robust average that may have no matches:
=IFERROR(AVERAGEIF(A2:A6, "South", C2:C6), "No data")

Live threshold total (threshold in F1, above the panel):
=SUMIF(C2:C6, ">"&F1, C2:C6)

If AVERAGEIFS throws #VALUE!, check that every range spans the same rows (all 2:6).

✅ Project Completion Checklist

SUMIF reports the correct total for one region
COUNTIF counts the Done tasks correctly
AVERAGEIFS uses multiple criteria with the sum/average range placed first
An IFS column labels every row and includes a TRUE catch-all so none returns #N/A
At least one summary cell is wrapped in IFERROR (or IFNA) for an expected empty case
Editing the underlying data updates every summary number live

🎯 Quick Quiz

Question 1: You switch a working SUMIF to SUMIFS to add a second condition, keeping the arguments in the same order — and it errors. What went wrong?

Question 2: Your IFS formula returns #N/A for a few rows. What's the cleanest fix?

Best Practices for Conditional Aggregation

✅ Do's

Keep all ranges the same size in the plural *IFS functions, or you'll hit #VALUE!.
Put comparison and wildcard criteria in quotes — the operator lives inside the string: ">=100".
Reference a cell for thresholds with the ">"&F1 pattern so the panel stays interactive.
Give IFS a TRUE catch-all so no row falls through to #N/A.

❌ Don'ts

Don't assume SUMIFS reads like SUMIF. The sum range moves to the front — the number-one mistake here.
Don't nest four IFs when IFS or SWITCH is clearer. Readability is a feature.
Don't wrap errors before you understand them. Confirm the formula is right first, then dress up expected edge cases.
Don't hard-code a total you could compute. A typed number won't move when the data does.

💡 Pro Tips

COUNTIFS with two conditions is a quick way to build a mini cross-tab (how many West + Done, East + Open, and so on) before you learn full pivot tables in Module 5.
To count blanks use COUNTIF(range, ""); to count non-blanks use COUNTIF(range, "<>"). Handy for data-quality checks.

📓 Learning Journal

Keep a learning journal as you work through this course — a document, a note, or even a tab in your own spreadsheet. After each lesson, take a few minutes to write down:

Key concepts you learned
Techniques that clicked for you
Questions or confusion points to revisit
Ideas you want to try in your own sheets
Your progress and feelings about learning this — including where your confidence grew

✍️ This lesson's prompt: Think about a dataset you actually have — expenses, a task list, a sales log, a habit tracker. Write down three real questions you'd want a summary panel to answer, then note which function each would need (SUMIF? COUNTIFS? AVERAGEIFS?). Did the SUMIF-versus- SUMIFS argument order trip you up? Capture the rule in your own words so it sticks.

📝 Lesson Summary

🎓 Key Takeaways

Conditional aggregation is a filter plus a math operation: keep the rows that match a criterion, then sum, count, or average them.
SUMIF / COUNTIF / AVERAGEIF take one condition; criteria can be text, numbers, comparison strings like ">100", or wildcards like "St*".
The plural SUMIFS / COUNTIFS / AVERAGEIFS take multiple criteria pairs — but the sum/average range moves to the front, the single biggest gotcha.
AND, OR, NOT build compound tests; IFS replaces ugly nested IFs (add a TRUE catch-all), and SWITCH maps exact values.
IFERROR catches any error and IFNA catches only #N/A — great for clean panels, but never use them to hide a genuine bug.

🎉 What You've Accomplished

You built a working summary panel that answers real questions about a subset of data and updates itself when the data changes. That's the beating heart of every dashboard — and you now have the exact toolkit (conditional sums, counts, averages, clean labels, and tidy error handling) that professionals use to turn raw rows into answers.

❓ Common Questions at This Stage

When should I use COUNTIFS instead of a pivot table?

COUNTIFS and its siblings are perfect for a handful of fixed questions on a live panel that always shows the same metrics. Pivot tables (Module 5) shine when you want to slice and re-slice interactively — dragging fields to explore. Many dashboards use both: pivots for exploration, *IFS for the pinned headline numbers.

My SUMIF over a number column returns 0 even though matches exist. Why?

Usually a data-type mismatch or a stray space. If the amounts are stored as text rather than numbers, they won't sum. Check the alignment (numbers align right by default) and consider re-entering or using VALUE. A trailing space in the criterion column also causes silent misses.

Is IFS available in my account?

IFS, SWITCH, IFERROR, and IFNA are all widely available in Google Sheets. If any returns a #NAME? error, double-check the spelling, and consult Google's current function list. You can always fall back to nested IF if needed.

🔭 Looking Ahead

In the next lesson — Lesson 4.4: QUERY — SQL-Like Power in a Single Cell — we meet Sheets' signature power feature. Instead of stacking several *IFS formulas, you'll write one QUERY that selects columns, filters, sorts, and groups all at once — a mini database language living inside a single cell. It's the natural next step up from conditional aggregation.

✅ Before the Next Lesson

Make sure your summary panel works and updates when you edit the data
Rewrite one SUMIF as a SUMIFS from memory to lock in the argument-order rule
Write your Learning Journal entry for this lesson

📚 Additional Resources

🌟 Encouragement for the Journey

You've now got the two pillars of real analysis: last lesson you learned to fetch a value, this lesson to summarize a group. Together they cover an enormous share of everyday spreadsheet work. If the SUMIFS argument order made you grumble, welcome to the club — even seasoned users double- check it. Next, one formula that does the work of many. 📊