How to make a pivot table with formulas in Excel

Q: How do I count instead of sum?

Swap SUMIFS for COUNTIFS and drop the sum range: COUNTIFS(A2:A13,r,B2:B13,c).

Excel formula

=LET(r,SORT(UNIQUE(A2:A13)),c,TRANSPOSE(SORT(UNIQUE(B2:B13))),VSTACK(HSTACK(A1,c),HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c))))

Give SUMIFS the unique regions as a column and the unique products as a row: =SUMIFS(C2:C13,A2:A13,SORT(UNIQUE(A2:A13)),B2:B13,TRANSPOSE(SORT(UNIQUE(B2:B13)))) fills the whole grid of totals.

Result on the example: a 4-row list that spills from E1 in E1
Works in: Excel 2024 and Microsoft 365
Google Sheets: Different formula, see below

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

r is the sorted regions as a column and c the sorted products turned into a row. When SUMIFS gets a column of regions and a row of products as criteria, it returns a grid with one total for each pair. VSTACK and HSTACK then add the labels: the header row (with "Region" from A1 in the corner) on top, the regions down the left.

In Google Sheets, QUERY's pivot clause builds the same table.

=LET(r,SORT(UNIQUE(A2:A13)),c,TRANSPOSE(SORT(UNIQUE(B2:B13))),VSTACK(HSTACK(A1,c),HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c))))

Result{4 rows × 4 cols} "Region", "Gadget", "Gizmo", "Widget", …

r{3 rows × 1 col} "East", "North", "West"
A name defined in LET, or a named range.
A2:A1312 rows × 1 col: "East", "West", "East", "North", …
A range: 12 cells in one column.
UNIQUE(A2:A13){3 rows × 1 col} "East", "West", "North"
UNIQUE: Distinct rows of a range. Spills.
- arrayA2:A1312 rows × 1 col: "East", "West", "East", "North", …
SORT(UNIQUE(A2:A13)){3 rows × 1 col} "East", "North", "West"
SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.
- rangeUNIQUE(A2:A13){3 rows × 1 col} "East", "West", "North"
c{1 row × 3 cols} "Gadget", "Gizmo", "Widget"
A name defined in LET, or a named range.
B2:B1312 rows × 1 col: "Widget", "Gadget", "Gizmo", "Widget", …
A range: 12 cells in one column.
UNIQUE(B2:B13){3 rows × 1 col} "Widget", "Gadget", "Gizmo"
UNIQUE: Distinct rows of a range. Spills.
- arrayB2:B1312 rows × 1 col: "Widget", "Gadget", "Gizmo", "Widget", …
SORT(UNIQUE(B2:B13)){3 rows × 1 col} "Gadget", "Gizmo", "Widget"
SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.
- rangeUNIQUE(B2:B13){3 rows × 1 col} "Widget", "Gadget", "Gizmo"
TRANSPOSE(SORT(UNIQUE(B2:B13))){1 row × 3 cols} "Gadget", "Gizmo", "Widget"
TRANSPOSE: Flips rows and columns.
- arraySORT(UNIQUE(B2:B13)){3 rows × 1 col} "Gadget", "Gizmo", "Widget"
A1"Region"
The value in A1.
HSTACK(A1,c){1 row × 4 cols} "Region", "Gadget", "Gizmo", "Widget"
HSTACK: Places ranges side by side.
- array1A1"Region"
- array2c{1 row × 3 cols} "Gadget", "Gizmo", "Widget"
C2:C1312 rows × 1 col: $4,200, $3,150, $1,900, $2,620, …
A range: 12 cells in one column.
SUMIFS(C2:C13,A2:A13,r,B2:B13,c){3 rows × 3 cols} 2440, 1900, 5350, 2960, …
SUMIFS: Adds values that meet every condition.
- sum_rangeC2:C1312 rows × 1 col: $4,200, $3,150, $1,900, $2,620, …
- criteria_range1A2:A1312 rows × 1 col: "East", "West", "East", "North", …
- criteria1r{3 rows × 1 col} "East", "North", "West"
- criteria_range2B2:B1312 rows × 1 col: "Widget", "Gadget", "Gizmo", "Widget", …
- criteria2c{1 row × 3 cols} "Gadget", "Gizmo", "Widget"
HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c)){3 rows × 4 cols} "East", 2440, 1900, 5350, …
HSTACK: Places ranges side by side.
- array1r{3 rows × 1 col} "East", "North", "West"
- array2SUMIFS(C2:C13,A2:A13,r,B2:B13,c){3 rows × 3 cols} 2440, 1900, 5350, 2960, …
VSTACK(HSTACK(A1,c),HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c))){4 rows × 4 cols} "Region", "Gadget", "Gizmo", "Widget", …
VSTACK: Stacks ranges on top of each other.
- array1HSTACK(A1,c){1 row × 4 cols} "Region", "Gadget", "Gizmo", "Widget"
- array2HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c)){3 rows × 4 cols} "East", 2440, 1900, 5350, …
LET(r,SORT(UNIQUE(A2:A13)),c,TRANSPOSE(SORT(UNIQUE(B2:B13))),VSTACK(HSTACK(A1,c),HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c)))){4 rows × 4 cols} "Region", "Gadget", "Gizmo", "Widget", …
LET: Names values inside a formula.
- name1r{3 rows × 1 col} "East", "North", "West"
- value1SORT(UNIQUE(A2:A13)){3 rows × 1 col} "East", "North", "West"
- name2, value2c{1 row × 3 cols} "Gadget", "Gizmo", "Widget"
- calculationTRANSPOSE(SORT(UNIQUE(B2:B13))){1 row × 3 cols} "Gadget", "Gizmo", "Widget"
- name2VSTACK(HSTACK(A1,c),HSTACK(r,SUMIFS(C2:C13,A2:A13,r,B2:B13,c))){4 rows × 4 cols} "Region", "Gadget", "Gizmo", "Widget", …

In Google Sheets

Google Sheets needs a different formula: =QUERY(A1:C13,"select A, sum(C) group by A pivot B",1). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMIFS(C2:C13,A2:A13,SORT(UNIQUE(A2:A13)),B2:B13,TRANSPOSE(SORT(UNIQUE(B2:B13))))Excel only	spills 3 × 3Different result	Just the grid of totals, for when the labels are already in place. The 3 by 3 block of totals, without labels.

Common mistakes

The second criteria must be a row (TRANSPOSE it). Two columns of criteria pair up row by row and return a single column instead of a grid.
Combinations with no sales show 0 in the formula version and a blank in QUERY.
Microsoft 365 has PIVOTBY for this, =PIVOTBY(A2:A13,B2:B13,C2:C13,SUM), which also adds totals. It isn't in Excel 2021 or 2024.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Make a pivot table with formulas

In E1, build a summary with the regions down the side, the products across the top and total sales in the grid, all from one formula.

Questions

How do I count instead of sum?

Swap SUMIFS for COUNTIFS and drop the sum range: COUNTIFS(A2:A13,r,B2:B13,c).

Why use formulas instead of a PivotTable?

The formula version recalculates instantly and can feed other formulas; a PivotTable needs refreshing but is quicker to rearrange.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.