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 formulas, not with Excel's PivotTable tool. One SUMIFS and mixed references fill 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

By Ali, Founder, Excel Lessons

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

SUMIFS can test more than one condition at once.

In `E2`, type: `=SUMIFS($C$2:$C$10,$A$2:$A$10,$D2,$B$2:$B$10,E$1)`. `$D2` locks the column, `E$1` locks the row.

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

SUMIFS can test more than one condition at once. In E2, type: =SUMIFS($C$2:$C$10,$A$2:$A$10,$D2,$B$2:$B$10,E$1). $D2 locks the column, E$1 locks the row.

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

In F2, copy the formula but end it with 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, total South Locks with $D3 and G$1.

Hint. $D3 and G$1.
5

In E4, add E2 and E3 with SUM.

Hint. The two cells above.
6

In G4, total the Locks column with SUM.

Hint. The two Locks cells.