Spreadsheet Practice

Lesson 32 of 45, Dynamic arrays

Self-building summary tables

Quick answer

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

Key formula
=UNIQUE(A2:A11) (Each region once)
Pro11 minutes, 3 exercisesUNIQUESUMIFSSORTBYSUMIF

Watch it done

1 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

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

=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

  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.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. List the regions
  2. Total for each region
  3. Ranked in one formula