Summarize by category and period

Fill a two-way summary grid from a single formula, reconcile its grand total against the rows and count the rows it leaves out, and explain what a summary built on a typed category list cannot notice.

A summary is where most people's spreadsheet stops and most readers' attention starts. It is also the last point at which a mistake can still be caught cheaply, because everything after it is a chart or a sentence built on top of it.

The failure specific to a summary grid is not arithmetic. Every cell in it can be individually correct while the grid as a whole is wrong, because a grid summarises the categories you typed into its headings, and the data has whatever categories it has. A site that opened in December, a product line added in the third quarter, a cost code somebody created last week — each appears in the data and in no row of the summary, and the total is short by exactly as much as nobody is looking for.

The whole defence is two cells: reconcile the grid against the rows, and count the rows the grid does not cover.

One formula fills the whole block. That is the test of whether the grid is built properly, and it is the mixed-reference pattern from Module 1 in the place it exists for.

=SUMIFS($D$2:$D$13, $B$2:$B$13, $A17, $C$2:$C$13, B$16)

Three ranges are anchored on both axes — the range being added and the two ranges being tested — because every cell of the block looks at the same rows. Two are anchored on one axis each: $A17 takes the site from column A and its row from wherever the formula sits; B$16 takes the month from row 16 and its column from wherever the formula sits.

Write that once in the top-left cell, copy it over the block, and every cell is right. Write it twelve times by hand and one of them will not be.

Row totals, column totals, and one grand total that has to agree with itself. Add the row totals across and the column totals down, and the grand total can be reached either way. If the two disagree, a cell in the middle is not what it should be. This is free and it is the fastest check available on a grid.

And then the check that matters. The grid's rows and columns are a list somebody typed. Reconcile the grand total against the rows:

=E21-SUM($D$2:$D$13)

Zero means the net totals agree, not that every row is covered. An omitted zero-valued row or offsetting errors can leave a zero difference. Negative means a net shortfall and positive a net excess; inspect the records and formulas to find the cause.

That number tells you there is a problem. A second cell tells you how big it is in rows rather than in units:

=COUNTA($A$2:$A$13) - (sum of COUNTIFS for each listed site/month pair)

Use a unique list of sites and a unique list of months so that each represented source row matches exactly one pair. A site-only count would miss an unlisted month.

"One session is not represented in the grid" is more actionable than "the total is eight short", because it points at a record rather than at an amount.

One detail makes the reconciliation real. The grand total must come from the grid and the comparison from the rows. If the grand total is written as a sum over the source column, the reconciliation subtracts that column from itself and reads zero forever. The same trap has appeared in three modules now, in three different disguises, and it is worth recognising by shape: a check that compares something with itself always passes.

What a native pivot does differently. Excel and Google Sheets both build this kind of summary for you, and the difference worth knowing is that a pivot discovers its categories from the data. Add a new site and it appears as a new row on the next refresh, which is exactly the failure this grid cannot notice on its own.

Two things a pivot does not fix. It caches its source range, so a pivot built when the table had 200 rows will happily summarise those 200 rows after 40 more are added, until somebody refreshes it and remembers to extend the range. And a pivot's refresh is an action a person has to take, so a printed pivot is as stale as the last time somebody clicked. The reconciliation cell is worth having next to a pivot too.

Twelve sessions, four sites, three months:

CR-501  Cedar     October    18
CR-510  Cedar     October    14
CR-511  Lakeside  December   11
CR-512  Lakeside  December    8

Lakeside opened in December. It has no October and no November, and a category list written in October would not contain it at all.

The grid, filled from one formula:

            October  November  December   Total
Cedar            32        20        22      74
Ridge            21        12         9      42
Hill             15        24        16      55
Lakeside          0         0        19      19
Total            68        56        66     190

Two cells in that grid are worth pausing on.

Lakeside's October figure is 0, and it is a real zero: the site existed as an idea and ran nothing. That is different from the cell being blank, and different again from Lakeside being missing from the grid, which is where this lesson's failure lives.

The grand total is 190 whether you add the row totals or the column totals. It has to be, and checking takes a second.

Someone changes session CR-512 (attendance 8) from Lakeside to Brookside — a site the grid has no row for.

Predict what each of these becomes.

the grand total of the grid
=E21-SUM($D$2:$D$13)              the reconciliation
=COUNTA($A$2:$A$13)               sessions in the data
the count of sessions the grid covers

A grid filled cell by cell:

B17   =SUMIFS(D2:D13,B2:B13,"Cedar",C2:C13,"October")
C17   =SUMIFS(D2:D13,B2:B13,"Cedar",C2:C13,"November")

Correct, and it will not survive being copied, edited, or having a month added. The category names are inside the formulas rather than in the headings, so the headings and the formulas can disagree without anything showing.

B17   =SUMIFS($D$2:$D$13,$B$2:$B$13,$A17,$C$2:$C$13,B$16)

Now the headings are the criteria. Rename a heading and the cell under it follows; the two cannot drift apart.

Then the total, and the trap:

E21   =SUM(E17:E20)          from the grid
G17   =E21-SUM($D$2:$D$13)   grid against rows

If E21 had been written =SUM($D$2:$D$13) it would show 190 today, look identical, and make G17 a subtraction of a number from itself. The grid could then be missing an entire row and the reconciliation would still read zero.

Whenever you write a check, ask the question that catches this: what would have to be wrong for this cell to stop being zero? If you cannot name something, the cell is decoration.

A quarterly summary has been sent to a funder. The programme lead notices that the total is about 4% below what the finance system says, and asks whether the summary or the finance system is wrong.

1. Reconcile before arguing. Put the grid's grand total and the row-level total side by side and subtract. A precise figure ends the discussion about whether there is a discrepancy and starts the one about where it is.
2. Count rows, not amounts. The amount tells you the size; the row count tells you how many records and therefore how findable they are. Eight units short could be one session or forty.
3. List the distinct categories in the data and compare against the headings. In a small table, sorting the category column and scanning is enough. In a large one, a COUNTIFS per site/month heading pair, totalled and compared against the row count, does it in one cell — which is the coverage line.
4. Check the ranges as well as the categories. A grid whose formulas cover rows 2 to 200 on a table that now has 240 is missing forty rows for a completely different reason, and reconciliation can reveal a net shortfall. An omitted zero-valued row does not change the attendance total. Use the covered-row check for omitted categories and independently audit the source ranges used by both checks.
5. Check for a net excess too. A positive reconciliation can result from duplicated headings or incorrect formulas. Inspect both axes and require unique headings; the sign alone does not prove the cause.

The general shape: a summary is a claim that these categories are all the categories. That claim is almost never checked and is wrong surprisingly often, and it costs two cells to check it every time.

Build the grid from one formula, add the totals, and then build the two coverage checks.

The last check moves a session to a site the grid has no row for, and requires both of them to notice.

Find a summary table somebody else produced — a report at work, a table in an article, a set of category totals on a dashboard.

1. Add up the category totals. Does the sum equal the stated total? If the table does not show a total, that is your first finding.
2. Ask whether the categories are exhaustive. Is there an "Other" row? If not, what happens to a record that fits none of them?
3. Ask whether the categories overlap. If a record could belong to two, the total may be counting it twice.
4. Find out when the table was produced and what range of records it covers, and say whether that is visible on the table itself.

Question 2 is where most of them fail. A table with no "Other" row is asserting that its category list is complete, and that assertion is almost never checked by whoever reads it.

Sign in to track your progress on this exercise.

Move on when you can do these.

• Write the mixed-reference formula for a two-way summary block, and say for each reference whether it moves down, across, both or neither.
• Say why the category names belong in the headings rather than inside the formulas.
• Describe the two coverage checks, and say what each one tells you that the other does not.
• Explain how a grand total written the wrong way makes a reconciliation unable to fail, and give the general shape of that mistake.
• Say what a native pivot does that this grid cannot, and name two things it does not fix.
• Say what a summary table with no "Other" row is asserting.

Sign in to track your progress on this exercise.

A summary grid is trustworthy when one formula fills it, its headings are its criteria, its grand total agrees whichever way it is reached, and separate net-total and two-axis row-coverage checks expose discrepancies. Unique headings, current source ranges and appropriate data values remain assumptions to verify.

Next: Choose and label a chart. The grid is now correct. The next step is drawing it — where every number can be right and the picture can still be false.