3.5Conditional calculation: SUMIF(S), COUNTIF(S)
The essentials in 30 seconds
- One condition = IF: =SUMIF(criteria_range,criterion,sum_range) adds up what meets one criterion — COUNTIF counts, AVERAGEIF averages.
- Several conditions = IFS: SUMIFS, COUNTIFS, AVERAGEIFS — and the range to aggregate comes first, before the criterion pairs.
- Frozen KPI or exploration: IFS for a targeted figure of the dashboard, pivot table to cross freely — you combine both.
Running mini-case — Wassim freezes his three KPIs without recalculating them
Wassim, an accountant in Algiers, recalculates by hand each week the Algiers total, the number of South sales and the East average basket — and reverses two ranges once in three. He freezes three formulas: a SUMIF on the region, a COUNTIFS on region + month, an AVERAGEIF on the product. Verified against his pivot table, they no longer move: the dashboard displays the KPIs live, and the pivot table remains the exploration tool.
The same case returns in the course, the diagram, the script and the 48h challenge.
1. Aggregating under ONE condition: the ...IF functions
SUMIF adds the values that meet a criterion: =SUMIF(Sales[Region],"Algiers",Sales[Amount]) gives the total of Algiers sales. Likewise COUNTIF (count: =COUNTIF(Sales[Region],"South")) and AVERAGEIF (average). The criterion can be a text, a number or a comparison in quotes (">1000"), or even a wildcard ("Keyboard*").
The order in simple ...IF: first the criteria range, then the criterion, then the sum range. It is this order — different from the ...IFS — that traps: Wassim learned it by reversing two ranges and getting zero without understanding.
2. Several conditions: the ...IFS — and when to prefer a pivot table
For several criteria, you use the ...IFS variants: SUMIFS, COUNTIFS, AVERAGEIFS. Watch the order of the arguments: =SUMIFS(Sales[Amount],Sales[Region],"Algiers",Sales[Amount],">1000") — the range to sum comes first, followed by the (criteria_range, criterion) pairs. All ranges must be the same size.
These functions are perfect for targeted indicators in a dashboard (one cell = one figure). To explore freely (cross several dimensions, change the angle), the pivot table remains more flexible. Wassim combines both: pivot table to explore, SUMIFS to freeze KPIs — and he verifies each against the other.
🔎 Concrete example — Total of Algiers sales above 1000
Wassim's «Sales» table (Region, Amount columns):
=SUMIF(Sales[Region], "Algiers", Sales[Amount])
=SUMIFS(Sales[Amount], Sales[Region], "Algiers", Sales[Amount], ">1000")
=COUNTIFS(Sales[Region], "South", Sales[Month], "2026-03")
=AVERAGEIF(Sales[Product], "Keyboard*", Sales[Amount])Reading: SUMIF puts the criteria range first; SUMIFS puts the sum range first.
Lesson: the order of the arguments differs between IF and IFS — a classic source of error.
Section vocabulary
- SUMIF
- Adds the values meeting ONE criterion: (criteria_range, criterion, sum_range).
Simple example: Wassim's Algiers total: =SUMIF(Sales[Region],"Algiers",Sales[Amount]).
What it is not: a sum of two criteria with ...IF.
Reflex: one criterion = ...IF.
- SUMIFS
- Adds according to SEVERAL criteria: range to sum FIRST, then the pairs.
Simple example: Algiers AND > 1000 in one formula.
What it is not: the range to sum last as in ...IF.
Reflex: sum range first, pairs next.
- COUNTIF(S)
- Counts the cells meeting one / several criteria — without adding them.
Simple example: The number of South sales: =COUNTIF(Sales[Region],"South").
What it is not: a monetary total obtained by counting.
Reflex: count rows = COUNTIF.
- AVERAGEIF(S)
- Average of the values meeting one / several criteria — the targeted average basket.
Simple example: Wassim's East average basket.
What it is not: an average over the whole column without a criterion.
Reflex: targeted average = AVERAGEIF.
- Criterion
- The condition in quotes or as a reference: text, number, comparison, wildcard.
Simple example: ">1000", "Algiers", "Keyboard*".
What it is not: a comparison without quotes (>1000 bare).
Reflex: comparison and text = quotes.
In practice — Building targeted indicators
- Calculate a category's total with SUMIF.
- Count a category's records with COUNTIF.
- Combine two criteria with SUMIFS (watch the order).
- Compare the result to a pivot table's to verify.
Ready-to-say script — explaining your KPIs (30 s)
«These three figures are frozen by formula: Algiers total with SUMIF, number of South sales with COUNTIFS, average basket with AVERAGEIF. In ...IFS, the range to sum comes first — I verified them against the pivot table, they are right to the cent.»
To say while presenting the dashboard; show the pivot table verification.
Copy-paste template — valid conditional KPIs
=SUMIF(Sales[Region], "Algiers", Sales[Amount])
=SUMIFS(Sales[Amount], Sales[Region], "Algiers", Sales[Month], "2026-03")
=COUNTIF(Sales[Region], "South")
=COUNTIFS(Sales[Region], "South", Sales[Month], "2026-03")
=AVERAGEIF(Sales[Region], "East", Sales[Amount])
=AVERAGEIFS(Sales[Amount], Sales[Region], "East", Sales[Product], "Keyboard*")Freelancer
Three KPIs frozen by IFS, verified against the pivot table: your steering holds without reprocessing.
SME in Algiers
Wassim freezes Algiers total, South sales, average basket: the dashboard lives on its own.
Project team
Standardize: one criterion = ...IF, several = ...IFS, cross-check with the pivot table.
Beginner
This week: a SUMIF, a COUNTIF, then an ...IFS with two criteria.
✓ Best practices
- One criterion → ...IF; several → ...IFS.
- In ...IFS, the range to aggregate first.
- Cross freely → pivot table; frozen KPI → IFS.
✗ Common pitfalls
- Reversing the order of the arguments between ...IF and ...IFS.
- Forgetting the quotes of a comparison criterion (">1000").
- Using ...IF for two conditions (it handles only one).
Key takeaways
- SUMIF/COUNTIF/AVERAGEIF: aggregate under ONE condition.
- ...IFS variants for SEVERAL conditions.
- In ...IFS, the range to aggregate comes first.
- Pivot table to explore; IFS for targeted KPIs.
48h challenge — apply tomorrow
Freeze 3 KPIs (SUMIF, COUNTIFS, AVERAGEIF) and verify them against your pivot table to the cent.
- My ...IF handle one criterion, my ...IFS several.
- My ...IFS ranges are first, same size.
- My KPIs are verified against the pivot table.
Frequently asked questions
Why does my SUMIFS return an error / 0?
Often the order of the arguments: in ...IFS the range to sum is 1st, then the (criteria_range, criterion) pairs. Also check that the ranges are the same size and that the comparison criteria are in quotes (">1000").
SUMIFS or pivot table?
SUMIFS for a precise figure, reusable in a dashboard (one cell = one KPI). Pivot table to explore and cross dynamically. The two are complementary.
How many conditions does SUMIF handle (without ...IFS)?