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 with headings in row 1: column A is merchant name, column B is order status, column C is order amount (RM). Five orders across two merchants. Columns E to G are for TechHub's delivered orders: their total, how many there are, and their average.
SUMIFS: total, filtered by every condition at once
Total order amount for TechHub, but only the delivered ones — two conditions, both must hold:
=SUMIFS(C2:C6,A2:A6,"TechHub",B2:B6,"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. Run it and watch the colours: the amount column, and each condition's range, light up in their own shade.
TechHub's delivered rows are 45 (row 2) and 90 (row 6) — row 3 is TechHub but returned, so it's excluded. What's the total?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Merchant | Status | Amount (RM) | Total | Orders | Average | |
| 2 | TechHub | delivered | 45 | =SUMIFS(C2:C6,A2:A6,"TechHub",B2:B6,"delivered") | |||
| 3 | TechHub | returned | 45 | ||||
| 4 | StyleCo | delivered | 30 | ||||
| 5 | StyleCo | delivered | 60 | ||||
| 6 | 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(A2:A6,"TechHub",B2:B6,"delivered")Same filtering logic as SUMIFS, minus the aggregation column.
Rows 2 and 6 are TechHub-and-delivered; row 3 is TechHub-and-returned. How many rows match both conditions?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Merchant | Status | Amount (RM) | Total | Orders | Average | |
| 2 | TechHub | delivered | 45 | =COUNTIFS(A2:A6,"TechHub",B2:B6,"delivered") | |||
| 3 | TechHub | returned | 45 | ||||
| 4 | StyleCo | delivered | 30 | ||||
| 5 | StyleCo | delivered | 60 | ||||
| 6 | TechHub | delivered | 90 |
AVERAGEIFS: the same filter, a mean instead of a total
=AVERAGEIFS(C2:C6,A2:A6,"TechHub",B2:B6,"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 G2, find TechHub's average order amount, delivered orders only.
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Merchant | Status | Amount (RM) | Total | Orders | Average | |
| 2 | TechHub | delivered | 45 | ||||
| 3 | TechHub | returned | 45 | ||||
| 4 | StyleCo | delivered | 30 | ||||
| 5 | StyleCo | delivered | 60 | ||||
| 6 | 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(C2:C6,A2:A7,"TechHub",B2:B6,"delivered") — one range accidentally extended to row 7 — 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.