Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use QUERY to group by multiple columns in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=SUMIFS(D2:D10,B2:B10,F2,C2:C10,G2)Google Sheets
=QUERY(A1:D10,"SELECT B, C, SUM(D) WHERE A IS NOT NULL GROUP BY B, C ORDER BY B, C LABEL SUM(D) 'Revenue'",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
- 1Prepare your table with at least three grouping-relevant columns — for example: Date (A), Region (B), Product (C), Revenue (D).
- 2Click an empty cell, such as F1, to anchor the output.
- 3Enter the formula: =QUERY(A1:D10,"SELECT B, C, SUM(D) WHERE A IS NOT NULL GROUP BY B, C ORDER BY B, C LABEL SUM(D) 'Revenue'",1).
- 4The output will have one row per unique (Region, Product) combination with a summed Revenue column.
- 5To add a count of transactions per group, change SUM(D) to SUM(D), COUNT(D) and add COUNT(D) 'Txns' to the LABEL clause.
Tips
- List the GROUP BY columns in the same order as the SELECT clause — mismatches cause a #VALUE! error.
- When grouping by a date column, you can use QUERY's year() or month() scalar functions: SELECT year(A), month(A), SUM(D) GROUP BY year(A), month(A) to roll up by year-month.
- QUERY results are dynamic — adding rows to your source automatically updates the grouped output if you used an open-ended range.
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
Is there a limit to how many columns I can GROUP BY?
Practically no hard limit, but each additional GROUP BY column multiplies the number of output rows. Beyond three or four grouping columns the result usually becomes harder to read than a proper pivot table.
Can I group by a calculated column in QUERY?
QUERY does not support arbitrary expressions in GROUP BY directly. You can pre-compute a helper column in your sheet, or use the scalar functions QUERY provides (year(), month(), day(), hour()).
How do I replicate multi-column GROUP BY in Excel without a pivot table?
Use SUMIFS with multiple criteria ranges: =SUMIFS(D:D, B:B, region_cell, C:C, product_cell). To generate the unique group list automatically, use the UNIQUE function (Excel 365) or a helper column.
More on QUERY (Google Sheets SQL)
See all →How-to
How to use QUERY to filter data
Use Google Sheets QUERY with a WHERE clause to filter rows dynamically — Excel users can use FILTER as the equivalent.
Error fixFix QUERY error
QUERY parse error in Google Sheets means the SQL-like query string has a syntax mistake. Find the exact error and fix it here.
How-toHow to use QUERY to sum by group
Use Google Sheets QUERY with GROUP BY and SUM to aggregate data by category — use SUMIF for the Excel equivalent.
How-toHow to use QUERY with WHERE, AND, OR
Filter QUERY results in Google Sheets using WHERE with AND and OR conditions. Excel users can replicate this with FILTER using * for AND and + for OR.
How-toHow to create a running total with QUERY
Build a running total in Google Sheets by combining QUERY for ordering with ARRAYFORMULA and SUMIF. Excel alternative uses a simple SUMIF with a locked anchor row.
How-toHow to combine QUERY with IMPORTRANGE
Combine QUERY and IMPORTRANGE in Google Sheets to filter and aggregate data from another spreadsheet file. There is no Excel equivalent — use Power Query to connect workbooks instead.