Formula example
Google Sheets QUERY GROUP BY and SUM
Use SELECT A, SUM(C) with GROUP BY A to combine repeated names into totals. Add ORDER BY SUM(C) DESC to turn the grouped results into a high-to-low leaderboard.
Copyable formula
=QUERY(A1:C9, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A ORDER BY SUM(C) DESC LABEL SUM(C) 'Total points'", 1)The formula returns Avery 33, Morgan 31, Jordan 28, and Riley 23 in descending order.
Useful variations
=QUERY(A1:C9, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A LABEL SUM(C) 'Total points'", 1)Groups each name and returns its total without forcing a ranking order.
=QUERY(A1:C9, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A ORDER BY SUM(C) DESC LABEL SUM(C) 'Total points'", 1)The main example: order the aggregated SUM(C) result, not the original Points column.
=QUERY(A1:C9, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A ORDER BY SUM(C) DESC LIMIT 3 LABEL SUM(C) 'Total points'", 1)Apply LIMIT after GROUP BY and ORDER BY to keep the three highest totals.
=QUERY(A1:C9, "SELECT B, A, SUM(C) WHERE A IS NOT NULL GROUP BY B, A ORDER BY B, SUM(C) DESC LABEL SUM(C) 'Total points'", 1)Every selected non-aggregated column, B and A, appears in GROUP BY.
=QUERY(A1:C9, "SELECT B, SUM(C) WHERE B IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC LABEL SUM(C) 'Total points'", 1)Returns North 64 and South 51 with the sample data.
=QUERY(A1:C9, "SELECT A, COUNT(C) WHERE A IS NOT NULL GROUP BY A LABEL COUNT(C) 'Entries'", 1)COUNT(C) counts non-empty Points values; in this sample, that equals two rows for each name.
=QUERY(A1:C9, "SELECT A, AVG(C) WHERE A IS NOT NULL GROUP BY A ORDER BY AVG(C) DESC LABEL AVG(C) 'Average points'", 1)AVG(C) returns the mean points for each name and sorts the averages high to low.
=QUERY({Jan!A2:C;Feb!A2:C}, "SELECT Col1, SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY SUM(Col3) DESC LABEL SUM(Col3) 'Total points'", 0)Use Col1, Col2, and Col3 when the source is a stacked array rather than a normal A1 range.
Sample data
| Name | Team | Points |
|---|---|---|
| Avery | North | 18 |
| Jordan | South | 12 |
| Avery | North | 15 |
| Morgan | North | 20 |
| Jordan | South | 16 |
| Morgan | North | 11 |
| Riley | South | 9 |
| Riley | South | 14 |
When to use this formula
- Use GROUP BY when repeated rows should become one summary row per name, team, product, or other category.
- Use ORDER BY SUM(C) DESC when the grouped total should rank from highest to lowest.
- Use QUERY for a report-shaped result with selected columns, aggregation, sorting, and labels in one formula.
How GROUP BY turns repeated rows into totals
SELECT A returns one group key for each distinct name, while SUM(C) adds the Points values in that group. Avery has 18 + 15 = 33, Morgan has 20 + 11 = 31, Jordan has 12 + 16 = 28, and Riley has 9 + 14 = 23.
To group by more than one field, select both fields and add both to GROUP BY. The team-and-name variation therefore uses GROUP BY B, A.
QUERY clause order and column notation
Keep the clauses in this order: WHERE, GROUP BY, ORDER BY, LIMIT, then LABEL. Put LABEL last even when the label is only changing the heading of an aggregate column.
Use A, B, and C for a normal A1 range. Use Col1, Col2, and Col3 for a stacked array such as {Jan!A2:C;Feb!A2:C} or an imported array.
Choose the right aggregation tool
QUERY is useful when the output needs grouping, selected columns, sorting, and labels. FILTER returns matching rows but does not aggregate them. For one name total, SUMIF is simpler; for multiple conditions, use SUMIFS; for an interactive summary, use a pivot table.
Excel does not support the Google Sheets QUERY function. In Excel, use a PivotTable, SUMIFS, or Power Query for the equivalent grouped report.
Leaderboard returned by the main formula
| Name | Total points |
|---|---|
| Avery | 33 |
| Morgan | 31 |
| Jordan | 28 |
| Riley | 23 |
Top 3 leaderboard
| Name | Total points |
|---|---|
| Avery | 33 |
| Morgan | 31 |
| Jordan | 28 |
Other grouped results
| Report | Returned result |
|---|---|
| Team totals | North 64; South 51 |
| COUNT(C) per name | Avery 2; Morgan 2; Jordan 2; Riley 2 entries |
| AVG(C) per name | Avery 16.5; Morgan 15.5; Jordan 14; Riley 11.5 |
Formula explanation
- SELECT A chooses the grouping key and SUM(C) adds the numeric Points values for each key.
- WHERE A IS NOT NULL prevents blank names from becoming an empty group.
- GROUP BY A is required because A is selected without an aggregate function.
- ORDER BY SUM(C) DESC sorts the grouped totals from largest to smallest; ORDER BY C is invalid for this grouped result because C is neither selected nor grouped.
- LIMIT 3 keeps the first three rows after the grouped totals have been sorted.
- The final 1 means the source range includes one header row. Use 0 when the query starts directly with data, as in the stacked-tab variation.
Common errors
- Every selected non-aggregated column must also appear in GROUP BY. If SELECT includes B and A, use GROUP BY B, A.
- Use the clause order WHERE, GROUP BY, ORDER BY, LIMIT, LABEL. Moving LABEL before LIMIT can cause a parse error.
- Sort an aggregate with ORDER BY SUM(C), not ORDER BY C.
- When a column mixes numbers and text, QUERY can treat the minority type as null. Clean the Points column so its values are consistently numeric.
- Use A, B, and C for a normal range, but use Col1, Col2, and Col3 for a stacked array or imported array.
- Use header argument 1 when the first row is headers and 0 when the source starts with data rows.
- Do not use FILTER for grouped totals. Use SUMIF for one simple criterion, SUMIFS for multiple criteria, or a pivot table for interactive summaries.
Build your own version
Use the formula builder for this pattern: Google Sheets QUERY Formula Builder.
Related formulas
Official references
- Google QUERY function from Google
- Google Visualization Query Language Reference from Google
FAQ
How do I use GROUP BY with SUM in Google Sheets QUERY?
Select the group column and SUM the numeric column, then add GROUP BY for the non-aggregated column: SELECT A, SUM(C) GROUP BY A.
How do I sort grouped totals from highest to lowest?
Sort the aggregate expression with ORDER BY SUM(C) DESC. Do not use ORDER BY C because C is the source column, not the grouped total.
How do I group by multiple columns?
Select each grouping column and list each one in GROUP BY, such as SELECT B, A, SUM(C) GROUP BY B, A.
What does ADD_COL_TO_GROUP_BY_OR_AGG mean?
It means a selected column is neither grouped nor aggregated. Add that column to GROUP BY, or wrap it in an aggregate such as SUM, COUNT, or AVG.
Why does QUERY ignore numbers stored as text?
When a column mixes numbers and text, QUERY can treat the minority type as null. Convert the Points values to real numbers and keep the column consistently numeric before using SUM, COUNT, or AVG.
Does Google Sheets QUERY work in Excel?
No. QUERY is a Google Sheets function. In Excel, use a PivotTable, SUMIFS, or Power Query for grouped totals and rankings.