Spreadsheet Practice

How to sum by group with QUERY in Google Sheets

Google Sheets formula

=QUERY(A1:D10,"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'",1)

Use QUERY with group by: =QUERY(A1:D10,"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'",1).

Result on the example
a 5-row list that spills from F1 in F1
Excel
See below

See it work

The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.

How it works

QUERY runs a small SQL-like statement on the range. select A, sum(D) group by A returns one row per region with its sales added up, order by sum(D) desc puts the biggest first, and label renames the heading. The final 1 tells QUERY the range has one header row.

=QUERY(A1:D10,"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'",1)

Result{5 rows × 2 cols} "Region", "Total sales", "West", 3055, …

  1. A1:D1010 rows × 4 cols: "Region", "Rep", "Product", "Sales", …

    A range: 10 rows × 4 columns.

  2. QUERY(A1:D10,"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'",1){5 rows × 2 cols} "Region", "Total sales", "West", 3055, …

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA1:D1010 rows × 4 cols: "Region", "Rep", "Product", "Sales", …
    • query"select A, sum(D) group by A order by sum(D) desc label sum(D) 'Total sales'"
    • headers1

In Excel

Excel has no QUERY. In Excel 365 use =GROUPBY(A2:A10,D2:D10,SUM); in any version, a PivotTable or a UNIQUE list with SUMIF gives the same totals.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=QUERY(A1:D10,"select A, sum(D) group by A",1)spills 5 × 2Different resultThe shortest form. Groups come out in alphabetical order with the heading "sum Sales". Sorted by region name, with the default heading.

Common mistakes

  • Every selected column that is not summed must be in group by. select A, B, sum(D) group by A returns an error.
  • Columns are letters (A, D) when QUERY reads a range, but Col1, Col4 when it reads the output of another formula.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Sum by group with QUERY

In F1, write one QUERY that returns each region with its total sales, largest first.

Questions

How do I group by two columns?

List both: select A, C, sum(D) group by A, C.

How do I add a condition?

Add a where clause before group by: select A, sum(D) where C = 'Laptop' group by A.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.