A Pivot Table with Rows and Columns

About this lesson

A pivot table with rows and columns has one group down the side and another across the top, and a total where each row meets each column. Here you build it with one SUMIFS and mixed references, so the same formula fills every cell of the table.

By the end you can

build a pivot table with rows and columns using SUMIFS.
use $D2 and E$1 so one formula fills a whole table.
total a summary table at the bottom.

Practises SUMIFS SUM

The idea

Rows and columns is the pivot table most people mean. Two conditions decide each cell: the row's region and the column's product. SUMIFS tests both.

The trick is in the references. $D2 locks the column, so filling to the right keeps reading the region. E$1 locks the row, so filling down keeps reading the product. One formula, written once in the top-left cell, fills the whole table when it is copied.

The mistake to watch for

Fixing both parts of every reference. With $D$2 and $E$1 the formula gives the same number in every cell, because nothing moves when it is filled. Lock only the column letter of the row label and only the row number of the column heading.

Where this comes up again

SUMPRODUCT: Weighted Totals and Conditions Summaries and Reports
The SUM Function Spreadsheet Basics
Fill Down and Fill Right Spreadsheet Basics
Relative References Cell References and Fill

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

A pivot table can have rows and columns: regions down the side, products across the top. In E2, the North Bikes total with SUMIFS: the sales to add, then the Region column and $D2, then the Product column and E$1. The dollar signs let one formula fill the whole table.

To begin, type it exactly:

=SUMIFS($C$2:$C$10,$A$2:$A$10,$D2,$B$2:$B$10,E$1)

Row	A	B	C	D	E	F	G
1	Region	Product	Sales		Bikes	Helmets	Locks
2	North	Bikes	400	North
3	South	Helmets	50	South
4	North	Locks	30	Total
5	South	Bikes	500
6	North	Helmets	70
7	South	Locks	40
8	North	Bikes	300
9	South	Helmets	100
10	South	Locks	10

Every step

1

A pivot table can have rows and columns: regions down the side, products across the top. In E2, the North Bikes total with SUMIFS: the sales to add, then the Region column and $D2, then the Product column and E$1. The dollar signs let one formula fill the whole table. To begin, type it exactly: =SUMIFS($C$2:$C$10,$A$2:$A$10,$D2,$B$2:$B$10,E$1).

Hint. The Sales column first, then Region with $D2, then Product with E$1.
2

In F2, the same formula for Helmets. Only the top-row reference changes: F$1.

Hint. F$1 at the end.
3

Filled down one row, the formula in E2 becomes =SUMIFS($C$2:$C$10,$A$2:$A$10,$D3,$B$2:$B$10,E$1). Read it and say what it returns.

Hint. The South Bikes sale.
4

In G3, South Locks: the same formula with $D3 and G$1.

Hint. $D3 and G$1.
5

In E4, the Bikes total: SUM over the two cells above.

Hint. The two cells above.
6

In G4, write a formula that totals the Locks column.

Hint. The two Locks cells.