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