2.4 Counting and summing with conditions
You can answer 'how much did this store sell in July' with one formula.
Before:01. Excel — First StepsUnlocks:03. Excel — Analysis
COUNTIFS and SUMIFS answer questions like how much one store sold in one month, straight from raw rows with a single formula. The topic follows lookups because criteria ranges reuse the same thinking, and it introduces the wildcard and comparison syntax all the conditional functions share. The common confusion is the argument order, which differs between SUMIF and SUMIFS just enough to bite. It ends with the honest question of when a PivotTable answers the same thing with less formula.
Work through these
COUNTIF/COUNTIFS and SUMIF/SUMIFS
Counting and totalling only the rows that meet conditions is what turns a table of transactions into an answer. These are the functions people use most once they are past the basics.
AVERAGEIFS, MAXIFS, MINIFS
The same conditional idea applied to averages, largest and smallest values. They complete the family and behave consistently with it.
Criteria syntax, wildcards and comparison operators
How conditions are written, including partial matches and comparisons, is where most mistakes occur. The syntax is fussy and worth practising on a small example.
When a PivotTable is the better answer
Past a certain number of conditional formulas, a pivot answers the same questions faster and stays correct as data grows. Knowing when to switch is part of knowing these functions.
Practice file
- xl-05-pivot-sales.xlsx
800 rows of fictional sales data for PivotTables, grouping, % of total and a first measure.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.