Day 11 — Summary tables — the formula behind a PivotTable
A PivotTable is faster to build. A formula-driven summary is the one that updates itself every month.
The report we are building
Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.
The goal is the table every manager asks for: region down the side, product across the top, revenue in the middle, totals around the edge.
One cell at a time
Every cell in that grid is a SUMIFS with two conditions — its own row label and its own column label.
That is the North row: 165,000 of laptops, 63,000 of monitors, 21,600 of keyboards, 7,200 of mice.
The row and column totals
256,800 + 219,000 + 329,000 + 263,200 = 1,068,000, which matches the grand total. Always check that your parts add up to your whole — it is the fastest way to catch a mistyped criteria.
825,000 + 198,000 + 28,800 + 16,200 = 1,068,000 again. The same total, sliced a different way.
Making the criteria point at cells
In a real sheet you would not type "North" into fifteen formulas. You put the labels in cells and lock the ranges:
$H3 locks the column so the row label always comes from H. I$2 locks the row so the column label always comes from row 2. Write that once in the top-left cell, drag it across and down, and the whole grid fills itself in. Every dollar sign in that formula is doing a job.
Counts and averages alongside
North: 5 orders, 40 units, average order 51,360, average price per unit 6,420. Revenue alone never tells the whole story.
Share of total
East is 30.8% of revenue; laptops are 77.2%. In a real sheet you would divide by $F$18 — an absolute reference to the total cell — so the percentage column can be dragged down safely.
SUMPRODUCT — when SUMIFS runs out
SUMIFS can only test whole ranges against criteria. It cannot multiply two columns together first. SUMPRODUCT can.
Line 2 gives 256,800 — the same as SUMIF(C2:C16,"North",F2:F16). Line 3 gives 165,000, matching the SUMIFS from earlier. SUMPRODUCT multiplies the TRUE/FALSE list by the amounts, so non-matching rows contribute zero.
Qty × Price only for one region, which SUMIFS simply cannot express.So when should you use a PivotTable?
| Exploring data you have not seen before | PivotTable |
| A one-off answer for a meeting | PivotTable |
| A report you rebuild every month | Formulas |
| Fixed layout someone else fills in | Formulas |
| Feeding numbers into another sheet | Formulas |
A PivotTable has to be refreshed, and it moves when the data grows. A formula grid sits still and recalculates on its own. For a recurring MIS pack, that difference is the whole job.
There is a full walkthrough of PivotTables in the free video lesson on the courses page.
Try these yourself
- Build the South row of the grid: laptop, monitor, keyboard and mouse revenue.
- Check that your four South numbers add up to the SUMIF total for South.
- Work out the East region’s average order value.
- Express West’s revenue as a percentage of the total, formatted to one decimal.
- Use SUMPRODUCT to total the revenue for East laptops only.