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.
Go Deeper on Dates and List Matching
Dates are the most common source of wrong COUNTIFS results. The dedicated SUMIFS date-range guide covers month-end formulas, timestamps and the text-date trap that silently returns 0.
If you are comparing two lists rather than totalling one, the compare two columns guide shows the COUNTIF, FILTER and conditional-formatting patterns. You can also paste both lists into the free Compare Lists tool — it runs in the browser and exports the differences as a CSV.
Build the formula without typing it in the Excel Formula Generator, or use the Formula Checker when an existing SUMIFS returns the wrong total. If you see a specific error code, the #VALUE! guide explains the usual causes.
Before you submit, paste the distinct COUNTIF and SUMIFS patterns into the Workbook Health Check and run the 23-point Assignment Readiness Check. They catch mismatched ranges, hard-coded criteria and the other issues that make a correct formula return a wrong total.
Need the matching rows rather than a total? The FILTER function guide covers single and multiple criteria, date ranges and the #CALC!/#SPILL! fixes.
