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 pivot-style summary with formulas in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=SUMIFS($C$2:$C$9,$A$2:$A$9,$E2,$B$2:$B$9,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 a flat data table with row category labels in A (e.g. Region), column category labels in B (e.g. Product), and values in C (e.g. Sales).
- 2Create the summary layout: list unique row labels in E2:E4 (e.g. East, North, South) and unique column labels in F1:H1 (e.g. Gadget, Widget, Other).
- 3In F2 enter =SUMIFS($C$2:$C$9,$A$2:$A$9,$E2,$B$2:$B$9,F$1). The data ranges are fully absolute ($); the row label uses a mixed reference ($E2 — column locked, row free); the column label uses a mixed reference (F$1 — row locked, column free).
- 4Copy F2 across to H2, then down to H4. Each cell computes the correct intersection total automatically.
- 5Add totals: =SUM(F2:H2) at the right of each row, =SUM(F2:F4) at the bottom of each column, and =SUM($C$2:$C$9) as the grand total. Verify all totals match.
Tips
- The mixed reference pattern ($E2 and F$1) is the single most important detail. Test it by copying one cell right and confirming the product label shifts, and one row down confirming the region label shifts.
- In Google Sheets, QUERY can build this summary in one formula: =QUERY(A1:C9,"select A, sum(C) group by A pivot B",1) — powerful but not portable to Excel.
- Keep the unique label lists (E2:E4 and F1:H1) driven by UNIQUE(A2:A9) and UNIQUE(B2:B9) in Excel 365 or Sheets so the summary expands automatically when new categories appear.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why use formulas instead of a pivot table?
Formulas update continuously without a manual refresh, can be embedded in dashboards with controlled layout, and work in templates or protected sheets where pivot tables cause permission issues. Pivot tables are faster to build for ad-hoc exploration.
Can I add a percentage-of-total column alongside the sums?
Yes — add a column dividing each intersection by the grand total: =F2/$I$5 (where I5 holds the grand total). Format as percentage. Use the same mixed-reference pattern so it copies correctly.
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 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.
How-toHow to group data by month using formulas instead of a pivot
Group and sum transaction data by month using SUMIFS with EOMONTH or TEXT in Excel and Google Sheets — no pivot table needed.