How to sum by month with QUERY in Google Sheets
Google Sheets formula
=QUERY(A1:F13,"select month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'",1)Group by month(A)+1: =QUERY(A1:F13,"select month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'",1).
- Result on the example
- a 3-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
QUERY's month() counts from 0, so January is 0 and July is 6. Adding 1 gives the usual month numbers, and grouping by that expression puts every July order in one row and every August order in another. The label clause must repeat the expression exactly, month(A)+1.
=QUERY(A1:F13,"select month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'",1)Result{3 rows × 2 cols} "Month", "Revenue", 7, 16260, …
A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …A range: 13 rows × 6 columns.
QUERY(A1:F13,"select month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'",1){3 rows × 2 cols} "Month", "Revenue", 7, 16260, …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 month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'" - headers
1
- data
In Excel
=GROUPBY(TEXT(A2:A13,"yyyy-mm"),F2:F13,SUM).Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =QUERY({ARRAYFORMULA(TEXT(A2:A13,"yyyy-mm")),F2:F13},"select Col1, sum(Col2) group by Col1 label Col1 'Month', sum(Col2) 'Revenue'",0) | spills 3 × 2Different result | Turning each date into year-month text first keeps different years apart and gives readable labels. Months shown as 2026-07 and 2026-08. |
| =QUERY(A1:F13,"select year(A), month(A)+1, sum(F) group by year(A), month(A)+1 label year(A) 'Year', month(A)+1 'Month', sum(F) 'Revenue'",1) | spills 3 × 3Different result | Group by year as well when the data covers more than one year. An extra Year column. |
Common mistakes
- Forgetting the +1 labels July as 6.
- month() alone merges the same month of different years. Group by year(A) too, or by yyyy-mm text.
- An open range like A1:F adds a blank group for the empty rows; add
where A is not null.
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 month with QUERY
H1, use QUERY to total the revenue for each month, with the month number in the first column (7 for July) and the headers Month and Revenue.Questions
How do I show month names instead of numbers?
Group by text: build TEXT(A2:A13,"mmm yyyy") in an array, as in the first alternative, but sort carefully, since text sorts alphabetically.
How do I sum by week?
Group by a week number from a helper column, such as =ARRAYFORMULA(WEEKNUM(A2:A13)), and query that.
How do I add a condition, like one region?
Add a where clause before group by: select month(A)+1, sum(F) where C = 'East' group by month(A)+1.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.