# Self-building summary tables (Excel lesson 32 of 45)

> A summary that lists its own groups, adds them up and ranks them.

Source: https://spreadsheetpractice.com/learn/summary-tables-unique-sumifs · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Dynamic arrays"
- **Time:** about 11 minutes, 3 graded exercises
- **Functions:** `UNIQUE`, `SUMIFS`, `SORTBY`, `SUMIF`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=UNIQUE(A2:A11)`: Each region once

## How it works

A report that you rebuild by hand every month is a report that goes stale. `UNIQUE` lists each region once, `SUMIFS` totals each one, and `SORTBY` ranks them. New regions appear on their own.

Step one: `=UNIQUE(A2:A11)`. Step two: a SUMIFS beside it, filled down, that totals the region in the same row. Step three folds it all into one formula by giving SUMIF the whole list of unique regions: `=SORTBY(UNIQUE(A2:A11),SUMIF(A2:A11,UNIQUE(A2:A11),C2:C11),-1)` returns the regions ranked by revenue.

## Syntax

| Formula | What it does |
|---|---|
| `=UNIQUE(A2:A11)` | Each region once |
| `=SUMIFS($C$2:$C$11,$A$2:$A$11,E2)` | Total for the region in E2 |
| `=SORTBY(UNIQUE(A2:A11),SUMIF(A2:A11,UNIQUE(A2:A11),C2:C11),-1)` | Regions ranked by total |

## Watch out for

- UNIQUE and SORTBY spill. Keep the cells below empty.
- The SUMIFS formula refers to the label cell, so the label list must sit in the same rows as the totals.
- A new region only appears if the ranges cover the new rows. Use a whole-column range or a Table.

## The exercises

Solve them in the live grid at [Self-building summary tables](https://spreadsheetpractice.com/learn/summary-tables-unique-sumifs). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **List the regions.** In E2, list each region from A2:A11 once.
2. **Total for each region.** The regions are in E2:E5. In F2, total the revenue for each region from A2:C11. Fill down to F5.
3. **Ranked in one formula.** In H2, return the regions ranked by total revenue, largest first, in a single formula. Use UNIQUE for the regions and SUMIF for their totals.

Previous: [LAMBDA with BYROW, MAP & SCAN](https://spreadsheetpractice.com/learn/lambda-byrow-map-scan) · Next: [ROUND, ROUNDUP & ROUNDDOWN](https://spreadsheetpractice.com/learn/round-roundup-rounddown)

---

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