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 sum by group in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=SUMIF($A$2:$A$6,A2,$B$2:$B$6)Google Sheets
=QUERY(A2:B6,"SELECT A,SUM(B) GROUP BY A",1)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Region | Sales |
| North | 120 |
| South | 80 |
| North | 200 |
| South | 150 |
| East | 90 |
=SUMIF($A$2:$A$6,A2,$B$2:$B$6)→320
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
- 1In Google Sheets, enter =QUERY(A2:B6,"SELECT A,SUM(B) GROUP BY A",1) in an empty area. QUERY groups all rows by column A and sums column B for each group.
- 2The result is a compact summary table that updates automatically when source data changes.
- 3In Excel, use SUMIF for each category: =SUMIF($A$2:$A$6,"North",$B$2:$B$6). Or use a PivotTable for a full grouped summary.
- 4In Excel 365+, SUMIF combined with UNIQUE can replicate the dynamic grouped output.
Tips
- In Google Sheets QUERY, add ORDER BY SUM(B) DESC to sort the groups by total in descending order: SELECT A,SUM(B) GROUP BY A ORDER BY SUM(B) DESC.
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
Can QUERY count rows per group instead of summing?
Yes. Replace SUM(B) with COUNT(B) in the QUERY string: SELECT A,COUNT(B) GROUP BY A.
How do I sum by group with two grouping columns in QUERY?
Add the second column to both SELECT and GROUP BY: SELECT A,B,SUM(C) GROUP BY A,B.
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 group by multiple columns
Use QUERY GROUP BY with multiple columns in Google Sheets to create multi-level summaries. Excel alternative uses SUMIFS with multiple criteria ranges.
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.