# How to sum by category with QUERY in Google Sheets

Group by the category and sum the amount: `=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1)`.

**Google Sheets:** `=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1)`

**Excel:** Excel has no QUERY function. In Excel 2021 or later, pair UNIQUE with SUMIFS: `=HSTACK(UNIQUE(C2:C13),SUMIFS(F2:F13,C2:C13,UNIQUE(C2:C13)))`. Microsoft 365 also has GROUPBY.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Product | Units | Revenue |
| 2026-07-03 | Ana Ruiz | East | Widget | 40 | 2080 |
| 2026-07-06 | Raj Patel | West | Gadget | 25 | 2225 |
| 2026-07-10 | Lee Chen | East | Gizmo | 12 | 1740 |
| 2026-07-14 | Ana Ruiz | North | Gadget | 30 | 2670 |
| 2026-07-17 | Kim Ortiz | West | Widget | 55 | 2860 |
| 2026-07-21 | Raj Patel | East | Widget Pro | 18 | 1422 |
| 2026-07-24 | Lee Chen | South | Gadget | 22 | 1958 |
| 2026-07-29 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| 2026-08-04 | Mo Diaz | North | Gizmo | 14 | 2030 |
| 2026-08-07 | Raj Patel | South | Widget | 20 | 1040 |
| 2026-08-12 | Kim Ortiz | North | Gadget | 16 | 1424 |
| 2026-08-18 | Mo Diaz | East | Gizmo Mini | 28 | 1092 |

Formula in H1: `=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1)`

Result (spills 5 × 2): Region, Revenue; East, $7,639; North, $6,124; South, $2,998; West, $5,085

## How it works

`group by C` makes one row per region, and `sum(F)` adds up the revenue in each. Every selected column must either be grouped or aggregated. The default header for the total is "sum Revenue", so `label` renames it. The regions come back in A to Z order.

## Other ways

- `=QUERY(A1:F13,"select C, sum(F) group by C order by sum(F) desc label sum(F) 'Revenue'",1)`: Order by the total to rank the regions. (Largest first: East, North, West, South.)
- `=QUERY(A1:F13,"select C, sum(F), count(F) group by C label sum(F) 'Revenue', count(F) 'Orders'",1)`: Add more aggregates as extra columns. (A third column with the number of orders.)

## Common mistakes

- Selecting a column that isn't in group by or inside an aggregate, such as `select B, C, sum(F) group by C`, returns #VALUE!.
- With an open range like A1:F, the empty rows form a blank group at the bottom. Add `where C is not null`.
- Numbers stored as text in the amount column are ignored by sum(). Make the whole column numeric.

## Questions

**How do I sum by two columns?**

Group by both: `select C, D, sum(F) group by C, D`.

**How do I add a grand total?**

QUERY has no total row. Stack one under it: `={QUERY(…);{"Total",SUM(F2:F13)}}`.

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-category-with-query