About this exercise
A 240-row dataset on one sheet, one answer cell, about 6 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- G2
=GROUPBY(C2:C241,E2:E241,SUM)
GROUPBY does what a PivotTable does with one field in Rows and one in Values: it finds every distinct region in C2:C241, adds up the amounts of each one, sorts the labels A to Z and appends a Total row — all from one formula in G2. Because it is a formula, the report recalculates the moment an amount, a region or a whole order changes, and a region that appears in the data for the first time gets its own row; a PivotTable shows such changes only after Refresh. The Total equals SUM(E2:E241) because every order belongs to exactly one region.
Pitfall 1: #SPILL! — the result needs every cell below and to the right of G2 to be empty; a stray value or an old formula there blocks it. Pitfall 2: header rows. Excel 365 notices a header in the selection (text above numbers) and leaves it out; this practice grid treats a selected header as data and shows an extra "Region" group with 0, so start at row 2 — that works in both. Pitfall 3: whole columns (C:C, E:E) also take in the empty rows below the data, which adds a blank group with 0. Optional arguments: =GROUPBY(C2:C241,E2:E241,SUM,0,0) drops the Total row (total_depth 0), and a sort_order of -1 lists the regions Z to A. In Excel 365, sort_order -2 sorts by the amounts; this grid ignores the column number in sort_order, so wrap the result in SORT instead (the tickets-per-agent exercise shows how). The PivotTable route in Excel: Insert → PivotTable, drag Region to Rows and Amount to Values (Sum is the default for numbers), then PivotTable Analyze → Refresh after the data changes. Version note: GROUPBY needs Excel for Microsoft 365; the one-time-purchase versions (Excel 2019, 2021 and 2024) do not have it, so there you need the PivotTable or a SUMIFS block with typed labels.
Other ways that work
- =GROUPBY(C2:C241,E2:E241,SUM,0,1) — the same report with field_headers 0 and total_depth 1 typed out
- =GROUPBY(C2:C241,E2:E241,SUM,0,1,1) — also sorting by the first column (A to Z) explicitly
- Without GROUPBY (Excel 2019, 2021 or 2024): typed region labels, =SUMIFS($E$2:$E$241,$C$2:$C$241,G2) filled down and a SUM for the Total row — the same numbers, but not accepted here because the task asks for GROUPBY
Common mistakes
Selecting whole columns, =GROUPBY(C:C,E:E,SUM): the empty rows below the data become an extra blank group with 0, so the report no longer matches the PivotTable; reference the data rows only.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 20, 2026. Author: GridBelt team.