A pivot table groups rows and totals each group: sales per region, spending per month. Spreadsheets build one from a menu (Insert, then PivotTable or Pivot table), and it's worth trying on your own data.
You can make the same summary with the formulas from this course, and it updates the moment the data changes. List each group once in a column, then use SUMIF, COUNTIF or AVERAGEIF with the group's name, and lock the data ranges with $ so the formula copies down cleanly: =SUMIF($B$2:$B$8,E2,$C$2:$C$8).
Newer versions can also list the groups for you with UNIQUE(B2:B8), which spills the answers into the cells below. Add a second condition with COUNTIFS or SUMIFS for two-way summaries, such as big deals per region.
Practice
Total the sales for each region in F2 (copied to F4).
A
B
C
D
E
F
1
Rep
Region
Sales
Region
Total
2
Ana
North
1200
North
3
Ben
South
800
South
4
Cara
North
950
East
5
Dev
East
400
6
Eli
South
1500
7
Fay
East
700
8
Gus
North
650
F2
More info
Quick reference
SUMIF / COUNTIF / AVERAGEIF
One line per group, ranges locked with $.
SUMIFS / COUNTIFS
Two-way summaries.
UNIQUE(range)
Lists each group once (newer versions).
The practice answers
Other formulas that give the same results count too.
Total the sales for each region in F2 (copied to F4). =SUMIF($B$2:$B$8,E2,$C$2:$C$8)
Now count the deals per region in G2 (copied to G4). =COUNTIF($B$2:$B$8,E2)
The average sale per region goes in H2 (copied to H4). AVERAGEIF works, and so does Total divided by Deals. =AVERAGEIF($B$2:$B$8,E2,$C$2:$C$8)
How many deals of 900 or more did each region make? Count them in F2 (copied to F4). =COUNTIFS($B$2:$B$8,E2,$C$2:$C$8,">=900")
An unofficial course. Not made by or affiliated with Microsoft; Excel is a trademark of Microsoft Corporation.