Chapter 3 · 7 levels
Conditional totals
Count and add up only the rows that match: SUMIF and COUNTIF, several conditions with SUMIFS, criteria strings and wildcards, MAXIFS and MINIFS, and a summary table from one formula.
StartMost questions a manager asks are not “what is the total?” but “what is the total for…”: for
the app, for completed orders, for the promo weekend, for orders over $250. In SQL you would add a
WHERE and a GROUP BY. In a spreadsheet you use the conditional functions: SUMIF, COUNTIF,
and their multi-condition cousins SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS.
In this chapter you work on Harbor & Pine’s Black Friday and Cyber Monday orders. You will count and
add up by one condition, then by several, write criteria such as ">=250" and "<>cancelled",
build a criterion from an input cell with &, use wildcards, and find the largest and smallest
order inside a date window. The boss level is the table every marketing team asks for after a
sale: revenue by channel and status, filled by one formula dragged across and down.
Everything here works the same in Microsoft Excel and Google Sheets.
Levels
- Count what's there with COUNTA, COUNTBLANK and COUNTIF
from George King, Sales Associate
count, counta, countblank, countif, distinct count
- App orders with COUNTIF and SUMIF
from Andrew Ramos, Marketing Manager
countif, sumif, criteria, conditional aggregates
- Several conditions with SUMIFS and COUNTIFS
from Susan Diaz, Growth Marketer
sumifs, countifs, averageifs, criteria, conditional aggregates
- Criteria strings, wildcards and a threshold cell
from Logan Brooks, Email Marketing Specialist
criteria, wildcards, concatenation, countif, sumifs
- What "<>" does with blank cells
from Chloe Gutierrez, Account Executive
criteria, blank cells, countif, countifs
- Biggest and smallest promo order with MAXIFS
from Frank Bell, Account Executive
maxifs, minifs, date criteria, criteria, concatenation
- Boss: the channel × status summary table
from Jordan Reyes, Head of Marketing
sumifs, mixed references, summary table, fill right, reconciliation