=QUERY(A:E,"select C, sum(E) group by C label sum(E) 'Total'",1) returns one row per category in column C with the total of column E, headed Total. QUERY takes a data range, a text instruction in a small query language, and the number of header rows. Learn the clauses once and you can answer most spending questions with a single formula.
What does each part of the formula mean?
Say your sheet has the date in A, merchant in B, category in C, account in D and amount in E, with headings in row 1.
| Part | Meaning | Note |
|---|---|---|
| A:E | The data to query | Include the header row |
| select C, sum(E) | Return the category and the total amount | Columns are named by their sheet letters |
| group by C | One output row per category | Every non-summed column in select must be grouped |
| label sum(E) 'Total' | Rename the result column | Single quotes around the new name |
| 1 | Number of header rows | Use 0 if your range has no headers |
The query string sits inside double quotes, which is why text values inside it use single quotes.
In what order must the clauses go?
QUERY is strict about order: select, where, group by, order by, limit, label, format. A where clause after group by produces a parse error. A fuller version of the formula, with the clauses in the right order:
=QUERY(A:E,"select C, sum(E) where C is not null group by C order by sum(E) desc label sum(E) 'Total'",1)
- where C is not null drops blank rows, so you do not get an empty-category line
- order by sum(E) desc puts the biggest category first
- limit 5 added after order by would keep only the top five
- format sum(E) '#,##0' shows whole rupees with thousands separators
How do I filter inside the query?
Add a where clause. To total only one account, such as a card in column D, write where D = 'Card'. To read a value from an input cell, break out of the string and join it back:
=QUERY(A:E,"select C, sum(E) where D = '"&H1&"' group by C order by sum(E) desc label sum(E) 'Total'",1)
The pattern is double quote, single quote, double quote, ampersand, cell, ampersand, double quote, single quote, double quote. Typing it once from this example and changing the cell reference is easier than rebuilding it from scratch. If H1 holds a number rather than text, drop the single quotes.
Why do I get an error or odd totals?
- Unable to parse query string: usually a clause is out of order, a text value is not in single quotes, or a column letter does not exist in the range
- A column is treated as text: QUERY decides each column's type by the majority of its cells, and values that disagree with the majority are dropped, so a few text amounts in a column of numbers can vanish from the sum
- Totals look low: the header count is wrong; with 1 given and no actual header, the first row is used as a heading
- Extra line with no category: add where C is not null
A worked example
Say a month has these rows in C and E: Groceries 2,300; Groceries 640; Fuel 1,950; Rent 18,000; Groceries 1,150. The formula returns Rent 18,000, Groceries 4,090, Fuel 1,950, in that order after the descending sort. The grand total of ₹24,040 should equal the plain sum of column E. When it does not, some amounts are text, or some rows have no category.
Setting it up
- Check the header row and note which letter holds each field
- Start with select and group by, then add one clause at a time
- Test the total against =SUM(E2:E)
- Put the formula in a cell with free space below and to the right
- Save a copy before experimenting
If you want grouped totals without writing queries, SpendixAI's Report builder filters and groups your transactions and lets you download the result as CSV.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

