The Four Functions, One Idea
| Function | Does | Use when |
|---|---|---|
COUNTIF | Counts cells meeting 1 condition | How many orders from the West? |
COUNTIFS | Counts with many conditions | West + Q1 + above $1,000? |
SUMIF | Sums with 1 condition | Total sales in the West? |
SUMIFS | Sums with many conditions | West + Q1 sales total? |
Syntax Without Tears
=COUNTIF(criteria_range, criteria) =SUMIF(criteria_range, criteria, [sum_range]) =COUNTIFS(range1, crit1, range2, crit2, ...) =SUMIFS(sum_range, range1, crit1, range2, crit2, ...)
Trap alert: in SUMIFS the sum_range comes first, unlike SUMIF. Mixing the order is the #1 cause of wrong totals.
6 Assignment-Ready Examples
1. Count orders by region
=COUNTIF(B2:B1000, "West")
2. Multi-condition count (region + quarter)
=COUNTIFS(B2:B1000, "West", C2:C1000, "Q1")
3. Sum with one condition
=SUMIF(B2:B1000, "West", D2:D1000)
4. Date-bounded totals (January sales)
=SUMIFS(D2:D1000, A2:A1000, ">=1-Jan-2026", A2:A1000, "<=31-Jan-2026")
5. Wildcards for partial text
=COUNTIF(A2:A1000, "*pro*") // contains "pro" =COUNTIF(A2:A1000, "???-2026") // pattern match
6. Dynamic criteria from a cell
=SUMIFS(D2:D1000, B2:B1000, $G$2, C2:C1000, $G$3)
Point criteria at dropdown cells ($G$2, $G$3) and your summary becomes interactive — an easy distinction-level touch.
Why Your Totals Are Wrong
- Unequal range sizes in COUNTIFS/SUMIFS → every range must have identical dimensions.
- Numbers as text →
">100"never matches text-numbers; convert the column first. - Hidden spaces →
"West "≠"West"; TRIM the source or use wildcards. - SUMIF vs SUMIFS order → sum_range is last in SUMIF, first in SUMIFS.
Averages with conditions? AVERAGEIF/AVERAGEIFS follow identical rules — swap them in and you're done.
