Pivot table with formulas: a SUMIFS cross-tab in Excel

Why build a pivot table with formulas

A PivotTable is the fastest way to explore data, but it has habits that get in the way of a fixed report: it has to be refreshed, its layout shifts when a new category appears, other formulas cannot easily point at its cells, and the styling is limited. A formula report has none of these problems. Every number is a live formula, the layout never moves, and you can put a chart, a target or a comment next to any cell.

Each part of a PivotTable has a formula twin: Rows and Columns become label lists, Values become SUMIFS (or COUNTIFS, AVERAGEIFS, MAXIFS), Filters become extra criteria, and Show Values As becomes a division.

Step 1: the row and column labels

The row labels are the distinct regions, the column headers the distinct categories. In Microsoft 365 or Excel 2021 and later, one formula each does it: =SORT(UNIQUE(A2:A13)) spills the regions down, and =TRANSPOSE(SORT(UNIQUE(B2:B13))) spills the categories across. In older versions, type the labels once. The UNIQUE function guide covers the details.

A distinct count, a PivotTable classic, also works without UNIQUE: =SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13)) returns 3 regions. With UNIQUE it is simply =ROWS(UNIQUE(A2:A13)).

Step 2: one SUMIFS for the whole grid

The value in each cell is the revenue where the region matches the row label and the category matches the column header:

=SUMIFS($C$2:$C$13,$A$2:$A$13,$A16,$B$2:$B$13,B$15)

The mixed references do the work. $A16 locks the column, so the formula always reads the region in column A. B$15 locks the row, so it always reads the category in the header row. Copy it from B16 to D18 and every cell picks up its own pair: North and Bikes give 1,840 + 1,260 = 3,100. With spilled labels, one formula does it all: =SUMIFS(C2:C13,A2:A13,H3#,B2:B13,I2#). If SUMIFS is new to you, start with the SUMIFS examples.

Worked example: 12 orders summarised by region and category

Rows 2 to 13 are the order lines (yellow inputs). Rows 15 to 19 are the cross-tab with one SUMIFS pattern and its totals, rows 21 to 24 show each cell as % of its row, and rows 26 to 29 as % of the grand total.

Worked example: 12 orders summarised by region and category

	A	B	C	D	E
1	Region	Category	Revenue
2	North	Bikes	1,840
3	South	Camping	620
4	North	Packs	310
5	West	Bikes	2,150
6	South	Bikes	1,480
7	West	Camping	540
8	North	Camping	780
9	South	Packs	260
10	West	Packs	390
11	North	Bikes	1,260
12	South	Camping	910
13	West	Bikes	1,730
14
15	Revenue	Bikes	Camping	Packs	Total
16	North	3,100	780	310	4,190
17	South	1,480	1,530	260	3,270
18	West	3,880	540	390	4,810
19	Total	8,460	2,850	960	12,270
20
21	% of row	Bikes	Camping	Packs	Total
22	North	74.0%	18.6%	7.4%	100.0%
23	South	45.3%	46.8%	8.0%	100.0%
24	West	80.7%	11.2%	8.1%	100.0%
25
26	% of grand total	Bikes	Camping	Packs	Total
27	North	25.3%	6.4%	2.5%	34.1%
28	South	12.1%	12.5%	2.1%	26.7%
29	West	31.6%	4.4%	3.2%	39.2%
30	Distinct regions				3

Fictional orders. The spill versions (UNIQUE, SORT, TRANSPOSE, H3#) need Microsoft 365 or Excel 2021 and later. The copied SUMIFS pattern works in every version.

Step 3: row totals, column totals and the check

A row total is the sum across the row, =SUM(B16:D16) = 4,190 for North, and a column total is the sum down the column: Bikes 8,460, Camping 2,850, Packs 960. The grand total in E19 is calculated directly from the data, =SUM(C2:C13) = 12,270, and it must equal both the sum of the row totals and the sum of the column totals. If it does not, a label is misspelled or missing and some rows fell through the criteria. A PivotTable never loses rows. A formula report can, so keep this check.

Step 4: Show Values As with plain division

• % of row total: divide each cell by its row total, =B16/$E16. North earns 74.0% of its revenue from Bikes, while South is split almost evenly between Bikes (45.3%) and Camping (46.8%).
• % of grand total: divide by the one grand total cell, =B16/$E$19. West Bikes alone are 31.6% of all revenue, and West is the largest region with 39.2%.
• % of column total: divide by the column total, =B16/B$19, to see how each category splits across regions.

Each % of row line adds up to 100%, which is a quick check that the divisor is locked correctly.

Filters, dates and the new PIVOTBY function

A report filter is one more criteria pair. With a channel in cell H1, add ,$D$2:$D$13,$H$1 to every SUMIFS. For an "(All)" option, use IF($H$1="(All)","*",$H$1) as the criterion, because the wildcard matches every text value. Date grouping works the same way: add a helper column with =TEXT(A2,"yyyy-mm") or a quarter key and use it as the column field.

Microsoft 365 also has GROUPBY and PIVOTBY, which build a whole summary from one formula. They are great for quick results, but the SUMIFS grid still wins when the layout must stay fixed, when you need custom totals or when the file has to work in older Excel versions.

Common mistakes

• Wrong dollar signs. $A$16 or B15 without the right lock makes every copied cell read the same label or drift off the header. Use $A16 and B$15.
• Unlocked data ranges. C2:C13 without $ shifts as you copy down, and the lower rows miss orders. Lock them: $C$2:$C$13.
• Labels that do not match the data. "Bike" in the header and "Bikes" in the data give 0. Generate the labels with UNIQUE, or check that the grand total matches.
• A grand total from the grid only. =SUM(B16:D18) hides rows that fell through the criteria. Compare it with =SUM(C2:C13).
• Dividing by the wrong total. % of row needs the row total ($E16), % of grand total the single total cell ($E$19). If a % of row line does not add up to 100%, the reference is wrong.
• Fixed ranges that stop short. New orders below row 13 are ignored. Use an Excel Table with structured references, or whole columns in SUMIFS.