Spreadsheets

QUERY to group expenses by month in Google Sheets

Group expenses by month in Google Sheets with QUERY: use year() and month()+1, avoid the zero-based month trap, and return readable month labels.

Editorial illustration for QUERY to group expenses by month in Google Sheets

To group expenses by month in Google Sheets, use QUERY with year(A) and month(A)+1 in both the select and the group by. The +1 matters because the query language counts months from zero, so January is 0 and April is 3. Without it every month is one behind.

What is the formula?

Assume transaction dates in A, descriptions in B, amounts in C, headers in row 1 and data down to row 500. The dates must be real dates, not text.

  • =QUERY(A1:C500, "select year(A), month(A)+1, sum(C) where C is not null group by year(A), month(A)+1 order by year(A), month(A)+1 label year(A) 'Year', month(A)+1 'Month', sum(C) 'Total spend'", 1)

It returns one row per year and month with the total. The labels go after order by, and the text inside them is in single quotes because the whole query sits in double quotes.

What is the zero-based month quirk?

Sheets' own MONTH function returns 1 to 12. The month() function inside the query string returns 0 to 11. So a 15 April transaction gives 3 in the query. If you see a table that starts at month 0 or ends at 11, you left out the +1. Add it in all three places where the month appears: select, group by and order by, otherwise the clauses refer to different things and the query fails.

How do I get readable month labels?

Numbers like 2025 and 4 are correct but not pretty. The simplest route is a helper column. In D1 type Month, and in D2 enter this and let it fill down:

  • =ARRAYFORMULA(TEXT(A2:A500,"yyyy-mm"))

Then query the helper:

  • =QUERY(A1:D500, "select D, sum(C) where C is not null group by D order by D label D 'Month', sum(C) 'Total spend'", 1)

Text in the form 2025-04 sorts in date order automatically, so April 2025 comes before May 2025 and the year changes cleanly. If you want Apr 2025 as the display, put a formula beside the result, such as =TEXT(DATE(LEFT(F2,4), RIGHT(F2,2), 1), "mmm yyyy"), where F2 holds the text month.

ApproachStrengthWeakness
year(A), month(A)+1No helper columnTwo number columns, not one label
Helper column with TEXTOne tidy label that sorts correctlyExtra column on the data sheet

How do I show only one financial year?

Add a where clause with date literals. In the query language a date is written with the word date and the format year-month-day:

  • where A >= date '2025-04-01' and A <= date '2026-03-31'

Placed before group by, this limits the result to the April 2025 to March 2026 year. A worked check: if April has 14,000 rupees, May 16,500 and June 12,500 in the data, the result has three rows, and adding the totals gives 14,000 plus 16,500 plus 12,500, which is 43,000.

Why is the month total missing or wrong?

  • Some dates are text. A date left-aligned in the cell is usually text. Convert it with DATEVALUE or re-enter it.
  • Blank rows inside the range. Keep where C is not null so empty rows do not create a blank group.
  • The amounts include credits. Total spend would then be net of refunds. Filter with where C > 0 if your debits are positive numbers, after checking your own sheet's sign convention.
  • Wrong range. If new rows go below row 500, extend the range or use A1:C.

What next?

  1. Add a second table for category by month using pivot in the query.
  2. Chart the result: select the table and choose Insert, Chart.
  3. Keep a note of the total of the whole column to reconcile against your statements.

When your statements arrive as separate files each month, a tracker that reads them directly spares you the copy and paste; SpendixAI lets you upload PDF, CSV or Excel statements and see monthly views in its reports.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports