A two-way SUMIFS table needs just one formula, written once in the top-left cell and filled right and down. The trick is mixed references: lock the column of the category label with $A2 and the row of the month header with B$1, so each copy picks up the right pair automatically.
What does the setup look like?
Entries sit on a sheet called Data: date in column A, category in column B, amount in column C, rows 2 to 500. The matrix sits on a sheet called Matrix. Categories go down A2:A9 (Food, Rent, Transport and so on). Month starts go across B1:G1, typed as real dates such as 01-Apr-2026, 01-May-2026 and formatted mmm-yy so they read as Apr-26. Type the first and let =EDATE(B1,1) in C1 fill the rest.
What is the one formula?
In B2, enter:
- =SUMIFS(Data!$C$2:$C$500,Data!$B$2:$B$500,$A2,Data!$A$2:$A$500,">="&B$1,Data!$A$2:$A$500,"<="&EOMONTH(B$1,0))
Read it from the left: sum the amounts, where the category equals the label in this row, where the date is on or after this column's month start, and on or before that month's last day. Sum range first, then each range and criterion pair, which is the argument order SUMIFS requires. Now drag from B2 across to G2 and down to row 9.
Why do $A2 and B$1 work, and what breaks them?
A dollar sign freezes whatever follows it. In $A2 the column is frozen, so moving right still reads column A, while the row is free to move down. In B$1 the row is frozen, so moving down still reads row 1, while the column moves right. The data ranges carry dollars on both sides, so they never shift.
- If every column shows the first month's number, you wrote B1 without the frozen row.
- If the first column is right and the rest are zero, the label reference is A2 and drifted to B2, C2.
- If everything is zero, your dates are probably text. Click a date cell: real dates align right by default.
- If the sheet name has a space, wrap it in single quotes, like 'My Data'!$C$2:$C$500.
How do I add totals and a check?
Put =SUM(B2:B9) at the bottom of column B and fill right, and =SUM(B2:G2) in column H and fill down. In the corner cell H10, the grand total, use =SUM(H2:H9). Then add a check, in another cell: =SUM(Data!$C$2:$C$500)-H10. It should be zero. If it is not, some entries carry a category that is not in your list, or a date outside the months shown. That gap is useful: it tells you something is uncategorised before you rely on the table.
A hypothetical three-month slice, with correct totals:
| Category | Apr (₹) | May (₹) | Jun (₹) | Total (₹) |
|---|---|---|---|---|
| Food | 8,200 | 7,900 | 9,100 | 25,200 |
| Rent | 18,000 | 18,000 | 18,000 | 54,000 |
| Transport | 2,400 | 2,100 | 2,700 | 7,200 |
| Total | 28,600 | 28,000 | 29,800 | 86,400 |
Rows and columns agree: 25,200 plus 54,000 plus 7,200 is 86,400, and so is 28,600 plus 28,000 plus 29,800.
What if my months are text labels?
You can build the same table with a helper column in Data: in D2 enter =TEXT(A2,"mmm-yy") and write the matching labels across the matrix, then use Data!$D$2:$D$500,B$1 as the third pair. It is simpler to read, though it depends on the text matching exactly, and the TEXT format codes follow the language settings of the computer. The date-boundary version above avoids that.
Checklist
- Dates are real dates.
- Category names in the matrix match the Data sheet spelling.
- Check cell equals zero.
- Mixed references typed once, then filled, never retyped cell by cell.
If you would rather skip building the grid, SpendixAI has a Report builder where you filter and group transactions, and the output downloads as CSV.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


