Chapter 05 · basic · 5 min
SUMIFS, COUNTIFS, AVERAGEIFS: conditional aggregation
SQL answers "total sales where status = 'delivered' and merchant = 'X'" with WHERE before GROUP BY. Excel's answer is the -IFS family: SUMIFS, COUNTIFS, AVERAGEIFS — plural, because each one takes as many criteria range/value pairs as you need, all of which must hold at once for a row to count.
We'll aggregate a small Shopee orders grid throughout: column A merchant, column B status, column C order amount.
The grid
An orders grid: column A is merchant name, column B is order status, column C is order amount (RM). Five orders across two merchants.
SUMIFS: total, filtered by every condition at once
Total order amount for TechHub, but only the delivered ones — two conditions, both must hold:
=SUMIFS(C1:C5,A1:A5,"TechHub",B1:B5,"delivered")The first argument is what gets summed. Every pair after it is a range and the value that range must match — this is WHERE merchant = 'TechHub' AND status = 'delivered' before the sum runs, not after.
TechHub's delivered rows are 45 (row 1) and 90 (row 5) — row 2 is TechHub but returned, so it's excluded. What's the total?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | TechHub | delivered | 45 | =SUMIFS(C1:C5,A1:A5,"TechHub",B1:B5,"delivered") | |
| 2 | TechHub | returned | 45 | ||
| 3 | StyleCo | delivered | 30 | ||
| 4 | StyleCo | delivered | 60 | ||
| 5 | TechHub | delivered | 90 |
COUNTIFS: same idea, no sum range
COUNTIFS drops the range-to-sum argument entirely — it just counts rows matching every condition pair:
=COUNTIFS(A1:A5,"TechHub",B1:B5,"delivered")Same filtering logic as SUMIFS, minus the aggregation column.
Rows 1 and 5 are TechHub-and-delivered; row 2 is TechHub-and-returned. How many rows match both conditions?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | TechHub | delivered | 45 | ||
| 2 | TechHub | returned | 45 | =COUNTIFS(A1:A5,"TechHub",B1:B5,"delivered") | |
| 3 | StyleCo | delivered | 30 | ||
| 4 | StyleCo | delivered | 60 | ||
| 5 | TechHub | delivered | 90 |
AVERAGEIFS: the same filter, a mean instead of a total
=AVERAGEIFS(C1:C5,A1:A5,"TechHub",B1:B5,"delivered")Identical argument shape to SUMIFS — first the range being aggregated, then the condition pairs — just a different aggregate at the end.
Average of TechHub's delivered orders
In E3, find TechHub's average order amount, delivered orders only.
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | TechHub | delivered | 45 | ||
| 2 | TechHub | returned | 45 | ||
| 3 | StyleCo | delivered | 30 | ||
| 4 | StyleCo | delivered | 60 | ||
| 5 | TechHub | delivered | 90 |
The trap: mismatched range sizes
Every range argument in a -IFS call must cover the same number of rows, or the pairing between them breaks silently rather than erroring. SUMIFS(C1:C5,A1:A6,"TechHub",B1:B5,"delivered") — one range accidentally extended to row 6 — doesn't fail loudly; it just misaligns which status goes with which amount for at least one row. Always select all the ranges the same way (drag the same set of rows, or use a table reference) so they can't drift apart.