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 QUERY in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Heads up: Excel and Google Sheets do this differently.
Excel
=SUMIFS(C2:C10,B2:B10,E2)Google Sheets
=QUERY(A1:C10,"SELECT B, SUM(C) WHERE A IS NOT NULL GROUP BY B LABEL SUM(C) 'Total'",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
- 1Ensure your raw data has a header row in row 1 — for example: Date (A), Category (B), Amount (C).
- 2Click an empty cell at least two columns away from your data — say E1 — where you want the summary to appear.
- 3Enter =QUERY(A1:C10,"SELECT B, SUM(C) WHERE A IS NOT NULL GROUP BY B LABEL SUM(C) 'Total'",1). The trailing 1 tells QUERY your data has one header row.
- 4QUERY will spill a two-column result: one row per unique Category with the summed Amount in the second column.
- 5To sort the result by descending total, add ORDER BY SUM(C) DESC before the LABEL clause: "SELECT B, SUM(C) WHERE A IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC LABEL SUM(C) 'Total'".
Tips
- Always include a LABEL clause when using aggregate functions like SUM or COUNT — without it the header Sheets generates is the raw expression (e.g. 'sum Col3') which looks ugly.
- Add WHERE A IS NOT NULL to prevent blank rows in your source range from creating a spurious blank-category row in the output.
- If your Category column contains numbers Sheets may treat the column as numeric. Cast it to a string with QUERY's FORMAT clause or use a TEXT column instead.
Need the Google Sheets version instead? Open Sheets variant in workspace →
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
Does QUERY work in Excel?
No. QUERY is Google Sheets-only and uses the Google Visualization API Query Language. In Excel, use SUMIFS to aggregate by category, or build a PivotTable from the Insert menu.
Can QUERY produce multiple aggregations at once?
Yes. SELECT B, SUM(C), COUNT(C), AVG(C) GROUP BY B will give you sum, count, and average side by side in one formula output.
What happens if I add new rows to my source data?
Use an open-ended range like A1:C instead of A1:C10 so QUERY automatically picks up new rows without needing formula changes.
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 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.