Challenge: quarterly sales summary

Summarise a deal log by region and month, work out shares, and let formulas name the winners. Advanced · 10 min · SUMIFS, Mixed references, % of total, INDEX/MATCH, MAX

Your sales team logs every deal in a simple list: month, region, rep and amount. That's great for recording, but the head of sales wants a one-glance summary of the quarter.

Turn the 20 rows in A1:D21 into a region-by-month table with totals and shares, then let formulas pick out the strongest region and month. Done right, the whole summary updates by itself when a deal is corrected.

> The trickiest part is the first step: one SUMIFS formula, with the right mix of $ signs, that fills the entire table.

Steps

Fill H2:J5 with the total sales for each region (rows) in each month (columns). Aim for one formula in H2 that can be filled across and down the whole block.
Add the totals: each region's quarter in K2:K5, and each month plus the grand total in H6:K6.
In L2:L5, show each region's share of the grand total, formatted as a percentage.
In H8, show the name of the region with the highest total. Work it out with a formula, so it changes if the numbers do.
In H9, do the same for the month with the highest total sales.

Remember

One SUMIFS with mixed references ($G2, H$1) can fill a whole two-way summary.
Share of total is part / $total$, with the total locked.
INDEX(names, MATCH(MAX(values), values, 0)) returns the label next to the biggest value.
Summaries built from formulas stay correct as the raw data changes.

Previous: Challenge: the class gradebook · Next: Challenge: stock room reorder list