# Capstone: a sales summary in Sheets (Google Sheets lesson 17 of 17)

> Build a working sales summary on one order log: a self-filling revenue column, a region table, a top-reps ranking and a drill-down.

Source: https://spreadsheetpractice.com/google-sheets/learn/capstone-sales-summary · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 20 minutes, 4 graded exercises
- **Functions:** `ARRAYFORMULA`, `SORT`, `UNIQUE`, `SUMIFS`, `QUERY`, `FILTER`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=ARRAYFORMULA(IF(A2:A="","",D2:D*E2:E))`: A whole calculated column from one cell

## How it works

This capstone puts the power tools together on one order log. Each task builds on the one before, the way a real summary sheet grows over a week of requests.

Start with a calculated column that fills itself. `=ARRAYFORMULA(IF(A2:A="", "", D2:D*E2:E))` multiplies units by price for every row that has a rep and leaves the rest blank, so a new order gets its revenue without anyone copying a formula down.

Then the summaries. `SORT(UNIQUE(B2:B13))` lists each region once, in order, and a SUMIFS beside it adds up revenue for each one. QUERY builds a grouped, ranked summary in a single formula. FILTER answers the follow-up question every summary gets: which orders make up that number?

Point every summary at the revenue column instead of multiplying units by price again, and give each result room to spill.

## Syntax

| Formula | What it does |
|---|---|
| `=ARRAYFORMULA(IF(A2:A="","",D2:D*E2:E))` | A whole calculated column from one cell |
| `=SORT(UNIQUE(B2:B13))` | Each region once, A to Z |
| `=SUMIFS($F$2:$F$13,$B$2:$B$13,H2)` | Revenue for the region in H2 |
| `=QUERY(A1:F13,"select A, sum(F) group by A order by sum(F) desc limit 3",1)` | Top three reps by revenue |
| `=IFNA(FILTER(C2:F13,B2:B13=L7,F2:F13>=L8),"No orders")` | The orders behind a number, with a fallback |

## Watch out for

- Without the IF, ARRAYFORMULA(D2:D*E2:E) puts a 0 in every empty row down the column.
- Don't type anything inside a spill area. One stray value blocks the whole result and the formula shows an error.
- UNIQUE treats "East" and "East " (with a trailing space) as two regions. Clean the text before you summarize it.
- SUMIFS ranges must be the same size. F2:F13 with B2:B14 returns an error.

## The exercises

Solve them in the live grid at [Capstone: a sales summary in Sheets](https://spreadsheetpractice.com/google-sheets/learn/capstone-sales-summary). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **A revenue column that fills itself.** In F2, write one ARRAYFORMULA that fills the whole Revenue column: units (D) times price (E) for every row with a rep in column A, and blank for rows without one. Use open-ended ranges that start at row 2.
2. **Revenue by region.** In H2, list each region from column B once, sorted A to Z. Then, in I2, total the revenue (column F) for the region in H2 with SUMIFS, and fill it down to I5.
3. **Top three reps.** In K1, use QUERY on A1:F13 to show the top three reps by total revenue (column F), biggest first, with the total column labeled "Revenue". Pass 1 for headers.
4. **The orders behind a number.** Your manager asks which orders make up a region's total. In K10, list the product, units, price and revenue (columns C to F) of the orders in the region typed in L7 with revenue of at least the amount in L8. Show "No orders" when nothing matches.

Previous: [SORTN, SEQUENCE and TRANSPOSE](https://spreadsheetpractice.com/google-sheets/learn/sortn-sequence-transpose)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
