# How to make a pivot table with formulas in Excel

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.

**Excel:** `=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))))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=QUERY(A1:C13,"select A, sum(C) group by A pivot B",1)`

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Region | Product | Sales |
| East | Widget | 4200 |
| West | Gadget | 3150 |
| East | Gizmo | 1900 |
| North | Widget | 2620 |
| West | Widget | 3880 |
| East | Gadget | 2440 |
| North | Gizmo | 1310 |
| West | Gizmo | 2075 |
| North | Gadget | 2960 |
| East | Widget | 1150 |
| West | Gadget | 990 |
| North | Widget | 1405 |

Formula in E1: `=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 (spills 4 × 4): Region, Gadget, Gizmo, Widget; East, $2,440, $1,900, $5,350; North, $2,960, $1,310, $4,025; West, $4,140, $2,075, $3,880

## 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.

## Other ways

- `=SUMIFS(C2:C13,A2:A13,SORT(UNIQUE(A2:A13)),B2:B13,TRANSPOSE(SORT(UNIQUE(B2:B13))))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/make-a-pivot-table-with-formulas