# How to sum by group with QUERY in Google Sheets

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

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Region | Rep | Product | Sales |
| West | Ana | Laptop | 1840 |
| East | Ben | Monitor | 420 |
| West | Cara | Monitor | 395 |
| North | Dev | Laptop | 1720 |
| West | Ana | Dock | 210 |
| East | Eli | Laptop | 1955 |
| West | Cara | Monitor | 610 |
| South | Fay | Monitor | 380 |
| East | Ben | Dock | 185 |

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

Result (spills 5 × 2): Region, Total sales; West, $3,055; East, $2,560; North, $1,720; South, $380

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

## Other ways

- `=QUERY(A1:D10,"select A, sum(D) group by A",1)`: The 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-by-group-with-query