Google Sheets QUERY GROUP BY: Sum by One or More Columns
QUERY can turn repeated region and category rows into a compact summary. The difficult part is usually defining each group and making sure the amounts reach the query as numbers.
Define one output row before writing the query
Place the input in A1:C8, with region in A, category in B, and numeric amount in C. Our output should contain one row for every distinct region-and-category combination. East/Books and West/Books remain separate even though they share a category.
Keep the raw rows. A summary cannot show which source records were combined unless you retain that detail elsewhere. The two downloadable files are an input fixture and its expected summary; the second file is a comparison reference, not another input to append.
| Summary you need | Selected columns | Grouping columns |
|---|---|---|
| Total for each region | A and sum(C) | A |
| Total for each category | B and sum(C) | B |
| Total for each region/category pair | A, B, and sum(C) | A, B |
| One grand total | sum(C) | No GROUP BY needed |
Group by two columns and name the result
Clear E1:G5 and enter the formula below in E1. The quoted query selects the two keys and an aggregate, filters out a missing region, groups the surviving rows, sorts the groups, and supplies readable labels. The final 1 describes the input header, not the number of result rows.
Use A, B, and C for this direct worksheet range. The words region and amount in row 1 are labels, not column identifiers to place in the query string. With an array-producing input, Col1, Col2, and Col3 are a useful positional notation; do not mix naming conventions in one query.
=QUERY(A1:C8,"select A, B, sum(C) where A is not null group by A, B order by A, B label A 'Region', B 'Category', sum(C) 'Total'",1)
Set input headers explicitly
For A1:C8, use headers = 1 because row 1 contains labels. If the data range instead starts at A2 and contains no header, use 0. Leaving the argument out, or using -1, lets Sheets infer a header count; a reliable template should not need that guess.
The headers argument and the LABEL clause solve different problems. The former identifies leading input rows; the latter names output columns. Setting headers to 0 does not mean an aggregate result will automatically have no label row. Keep clear output labels for this report and compare the four data rows beneath them.
Check numbers before investigating a missing total
Google documents that QUERY assigns a single type to each input column using its majority type. Values of minority types are treated as null. A number stored as text can therefore disappear from a numeric aggregate even though it looks like an ordinary amount.
In a helper column, use =ISNUMBER(C2) and fill through C8. For this fixture every amount should be numeric. If the 15 in the second East/Books record is changed to the text '15 while the other amounts remain numeric, the expected East/Books total becomes 40 under the documented typing rule. Repair the source type instead of adding an unexplained 15 to the summary.
Explain blank groups and grouping errors
The sample's missing region is excluded by where A is not null. Its amount is still part of the raw source total. If blank regions represent legitimate business data, remove that filter or assign a reviewed category such as Unassigned before summarizing. Do not silently discard them just to make the report look tidy.
If you select A, B, and sum(C) but group only by A, each region may contain several category values and the query cannot choose one B for the result. Add B to GROUP BY or remove it from SELECT. Keep aggregate expressions such as sum(C) out of the grouping list; they are calculated after the source groups are formed.
Reconcile the output and keep the range current
Compare the output with the expected CSV: four groups, totals 55, 30, 20, and 30, and a combined amount of 135. Separately reconcile the excluded amount 9 to reach the original 144. This checks both aggregation and the deliberate filter.
A1:C8 includes only this fixture. Expand the range when new records arrive, and leave space for new output groups. Keep a consistent type and spelling in each key column; trailing spaces can split what appears to be one category. The Column Harbor CSV checker does not execute QUERY or verify the stored cell types in your live sheet.
Common questions
Can QUERY group by more than one column?
Yes. Include all required key columns in GROUP BY and include each non-aggregated selected column there too. This guide groups by region and category together.
Why does the first source record disappear?
Check the input range and headers argument. A header count of 1 on a range that begins with data tells QUERY to treat that first record as a header. Also inspect filters and minority data types.
Why does changing a label not fix a missing-column error?
LABEL changes result headings. It does not rename the source identifiers used by SELECT or GROUP BY. Use the worksheet column letters or the positional Col notation appropriate to your input.
Sources & method
- Google Docs Editors Help: QUERY function ↗
- Google for Developers: Query Language Reference ↗
- Google Docs Editors Help: Using arrays ↗
Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.