Answer grouped questions

Produce grouped sums and counts whose criteria match every row they should, choose the denominator the question asks for, reconcile the group totals back to the rows, and surface a zero denominator rather than reporting a share of nothing.

Almost every wrong figure in a report is right arithmetic over the wrong set of rows.

Two things cause it, and neither looks like an error. A criterion that matches fewer rows than it should — because two site names were typed with a trailing space — produces group totals that are each individually plausible and collectively short. And a share divided by the wrong denominator produces a percentage that is arithmetically perfect and answers a question nobody asked.

The reason these survive review is that the numbers look fine. Nothing is red. Start with a reconciliation: add the groups and compare them with the source rows. A nonzero difference needs investigation; zero shows net agreement, so also audit matched row counts, category headings and source ranges.

SUMIFS and COUNTIFS answer the question "for the rows where these conditions hold, what is the total, and how many are there?"

=SUMIFS(what to add, where to test, what to test for, ...)
=COUNTIFS(where to test, what to test for, ...)

SUMIFS names the column to add first; COUNTIFS has nothing to add, so it starts with the first condition. SUMIF puts its optional sum range last, unlike SUMIFS. COUNTIF and COUNTIFS both begin with a criteria range and its criterion; COUNTIFS can add more pairs. All of them are fine; mixing up the order is the commonest reason one returns a number that looks odd.

A criterion can be a value ("Cedar", or a cell holding it), a comparison written as text (">=12"), or a pattern ("Ridge*").

A criterion is a value, not a transformation. This is the constraint that shapes everything else. You cannot write TRIM(B2:B11) inside SUMIFS and have it tidy the column as it goes. If the category column has a trailing space on some rows, those rows do not match the exact listed headings. In this exercise, use a tidied column of its own, sitting where a reader can see it. That column is not clutter; it is the record of what was cleaned.

The denominator is the question. Given a per-site table, at least two different shares can be computed and they answer different questions:

• This site's attendance divided by all attendance. "How much of the programme happens here?" The denominator is the whole.
• This site's successful sessions divided by this site's own sessions. "How well does this site do?" The denominator is the group.

Both are correct. Only one answers any particular question, and a column labelled just "%" is unreadable because it does not say which. Label the denominator in the heading: "share of all attendance" and "share of own sessions" cannot be confused; "percentage" and "rate" can.

A group with no rows has no within-group rate. A site that has not opened yet has a session count of zero. Its summed attendance is zero because the group is empty, and its share of a positive all-attendance total is validly zero. Its success rate is zero divided by zero, which is not a number and must not be made to look like one. #DIV/0! there is the correct output. Wrapping it in IFERROR(...,0) reports that none of the site's sessions met their target, which is a statement about sessions that never happened.

Reconcile. Add the group totals and subtract the row-level total. Require zero, exactly. This can reveal a net shortfall or excess from omitted rows, duplicate grouping, truncated ranges or missing categories. It does not prove complete coverage: omitted zero values or offsetting mistakes can leave a zero difference. Audit row counts, unique headings and source ranges separately.

One detail makes the reconciliation real rather than decorative: the row-level total must come from the rows. If it is computed as the sum of the group totals, the reconciliation compares the groups with themselves and can never fail.

Ten sessions across three sites, two of which were typed untidily:

        B                D          E
      Site as entered  Attended  Met target
CR-401  Cedar             18        yes
CR-409  Hill␣             12        no       trailing space
CR-410  Cedar␣            16        yes      trailing space

Grouping on column B directly gives Cedar 50, Ridge 48 and Hill 39 — total 137. The rows add to 165. Twenty-eight attendances are in the data and in no group, and every individual figure in that table looks entirely reasonable.

Adding a tidied column and grouping on that gives Cedar 66, Ridge 48 and Hill 51 — total 165, and the reconciliation reads zero.

The gap is worth putting a number on rather than just closing. "Grouping on the untidied column would have understated Cedar by sixteen" is a sentence that gets a data-entry process fixed. "I cleaned it" is not.

The tidied summary: Cedar has 4 sessions and 66 attendance, of which 3 sessions met target. Across all sites there are 10 sessions and 165 attendance, of which 7 met target. Lakeside has 0 sessions and 0 attendance.

Predict these four.

=I2/$I$6          Cedar attendance over all attendance
=K2/H2            Cedar successes over Cedar sessions
=K2/$H$6          Cedar successes over all sessions
=K5/H5            Lakeside successes over Lakeside sessions

A first attempt at the per-site attendance:

=SUMIFS($D$2:$D$11,$B$2:$B$11,G2)

It is correctly written and it silently drops two rows, because two of the site names in column B end in a space and no criterion of "Cedar" will ever match "Cedar ".

Add the tidied column and point at it instead:

F2  =TRIM(B2)                                     copied down
I2  =SUMIFS($D$2:$D$11,$F$2:$F$11,G2)

Then add the net-total check:

N2  =SUM(I2:I5)-I6        where I6 is =SUM(D2:D11)

Zero means the totals agree in net. Negative means a net shortfall; positive means a net excess. Neither sign identifies a unique cause, and zero does not prove every row is in exactly one group. Check row membership and source ranges as well as amounts.

The mistake to avoid in that last cell is subtle. If I6 is written as =SUM(I2:I5) — the total of the groups rather than of the rows — the reconciliation becomes SUM(I2:I5)-SUM(I2:I5), which is zero forever, whatever is wrong with the grouping. It gives the same number today as the correct version, which is exactly why it survives review.

A board paper says three sites account for 100% of attendance, and separately reports total attendance as 165. Adding the three site figures gives 137. Both numbers came from the same sheet.

1. Reconcile before investigating. 165 minus 137 is 28. That is not a rounding difference or a definitional subtlety; twenty-eight attendances are in the rows and in no group.
2. Count the rows each group matched. Add a COUNTIFS beside each SUMIFS. If the group counts add to eight and the table has ten rows, two rows match no criterion.
3. Find them by elimination, not by eye. Add a column that flags any row whose category is not in the group list, or simply count rows matching each criterion and look for the shortfall. Two rows will have a category that looks correct on screen.
4. Look at the length, not the letters. A trailing space is invisible. LEN is not. Comparing the length of the entered value against the length of its tidied version identifies the rows in one step.
5. Ask how the 100% happened. If the shares were computed as each group over the sum of the groups, they will add to 100% no matter how many rows are missing. A share should be measured against the row-level total, so that a missing row shows up as shares that fall short of the whole.

That last point is the reusable one: a set of shares that always adds to 100% is not evidence of anything. Shares that add to 83% are telling you something important.

Build the per-site summary. Two of the site names are typed untidily, one site has no sessions at all, and two of the columns are shares that answer different questions.

Finish with the reconciliation and the two diagnostic cells that say what the tidying was worth.

Take a report you have seen recently that contains a percentage — a dashboard at work, a news chart, an annual report.

1. Write down what the percentage's denominator is. If the report does not say, that is your finding, and it is a common one.
2. Write down a different denominator that could plausibly have been used, and say roughly how the number would change.
3. Say which of the two answers the question the report is actually making a claim about.
4. Check whether the parts add to exactly 100%. If they do, ask whether that is because they cover everything, or because each part was divided by the sum of the parts.

Question 4 is the one worth practising. Shares that always total 100% are often computed in a way that guarantees it, and that guarantee is exactly what hides a missing category.

Sign in to track your progress on this exercise.

An assistant is quick and reliable at the mechanics here, and useless at the judgement.

Ask it about argument order. "In SUMIFS, does the range to add come first or last, and how does that differ from SUMIF?" That is a fact, it is easy to get wrong from memory, and you can check the answer in five seconds by running the formula on a range you already know the total of.

Ask it to explain a criterion. "What does the criterion ">=12" match, and how would I write it if the threshold were in cell I2?" — the answer, that you concatenate as ">="&$I$2, is the kind of syntax that is genuinely hard to guess and trivial to verify.

Do not ask it which denominator you want. It cannot know what question your report is answering, and it will pick one and explain it convincingly. That is the decision the whole lesson is about, and it is yours.

Move on when you can do all of these.

• Say which argument comes first in SUMIFS and how that differs from SUMIF.
• Explain why a criterion cannot tidy the text it is matching against, and what to do instead.
• Given a per-group table, write two different shares and say what question each answers.
• Say what should appear when a group has no rows and you divide by its count, and why hiding it is worse.
• Write a reconciliation cell and say what a positive value and a negative value each mean.
• Explain why a set of shares that always adds to 100% may be evidence of nothing.

Sign in to track your progress on this exercise.

Group with criteria that match every row you meant, label each share with its denominator so nobody has to guess, let an empty group show its division error, and reconcile the groups against the rows before believing any of it.

Next: Join reference information. Grouping works within one table. The next step is attaching a second one — where the failures are a code that matches nothing, a code that matches twice, and a code that looks identical to another and is not.