How to sum by category with QUERY in Google Sheets
Google Sheets formula
=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1)Group by the category and sum the amount: =QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1).
- Result on the example
- a 5-row list that spills from H1 in
H1 - Excel
- See below
See it work
The formula is already in H1. Change any input and the result updates, or edit the formula to experiment.
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.
=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1)Result{5 rows × 2 cols} "Region", "Revenue", "East", 7639, …
A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …A range: 13 rows × 6 columns.
QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1){5 rows × 2 cols} "Region", "Revenue", "East", 7639, …QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.- data
A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", … - query
"select C, sum(F) group by C label sum(F) 'Revenue'" - headers
1
- data
In Excel
=HSTACK(UNIQUE(C2:C13),SUMIFS(F2:F13,C2:C13,UNIQUE(C2:C13))). Microsoft 365 also has GROUPBY.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =QUERY(A1:F13,"select C, sum(F) group by C order by sum(F) desc label sum(F) 'Revenue'",1) | spills 5 × 2Different result | 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) | spills 5 × 3Different result | 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.
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 category with QUERY
H1, use QUERY to total the Revenue for each region, with the total column headed Revenue.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)}}.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.