Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to build a cross-tabulation with formulas in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=SUMIFS($C$2:$C$7,$A$2:$A$7,$E2,$B$2:$B$7,F$1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Set up flat data: A = Region (row dimension), B = Quarter (column dimension), C = Sales (values), rows 2-7.
- 2In E1:H1 place your cross-tab header: E1='Region', F1='Q1', G1='Q2', H1='Q3'.
- 3In E2:E4 list your row labels: 'East', 'West', 'North'.
- 4In F2 enter =SUMIFS($C$2:$C$7,$A$2:$A$7,$E2,$B$2:$B$7,F$1). The mixed references lock the data range absolutely, lock the row label column ($E), and lock the column header row ($1).
- 5Copy F2 across to H2, then down to row 4 — the formula populates the entire cross-tab grid.
Tips
- Use mixed references carefully: $E2 locks the column but lets the row change; F$1 locks the row but lets the column change — this is what allows one formula to fill the whole grid.
- Wrap in IFERROR(...,0) to replace #VALUE! or blanks with zero for clean presentation.
- Add a totals row below and totals column to the right using SUM across the cross-tab range — these update automatically as the SUMIFS values change.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How is this different from a pivot table cross-tab?
The formula cross-tab updates instantly when source data changes and lets you control exactly which rows and columns appear. A pivot requires manual refresh and auto-generates its row/column headers from the data.
Can I average instead of sum in the cross-tab?
Replace SUMIFS with AVERAGEIFS using the same structure: =AVERAGEIFS($C$2:$C$7,$A$2:$A$7,$E2,$B$2:$B$7,F$1).
What if my row or column labels are not known in advance?
Use UNIQUE to generate distinct row and column labels dynamically, then reference those in your SUMIFS grid.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.