A merchant-by-month grid needs one formula, filled across and down: SUMIFS with the merchant and the month's first and last dates as conditions. Unlike a pivot table, it updates the moment your data changes, and you control every label. Here is how to build it from scratch.
Why use a formula instead of a pivot table?
A pivot gives speed; a formula grid gives control and live updating. The volunteer treasurer of a village hall, say, wants a sheet that her successor can open and read without knowing how to refresh a pivot. Merchants in a fixed order, months in a fixed order, and totals that never go stale.
How should the data be laid out?
| Column | Holds | Notes |
|---|---|---|
| A | Date | Real dates, not text |
| B | Merchant | One spelling per supplier |
| C | Amount | Numbers only, refunds negative |
Rows 2 to 500 hold the data. Keep it in one block with no gaps.
What does the grid formula look like?
Put merchant names down column J starting at J2. Across row 1, from K1, type the first day of each month as a real date: 1 September 2026 in K1, 1 October 2026 in L1, 1 November 2026 in M1. Format those cells as mmm yyyy so they show as month names. Then in K2:
=SUMIFS($C$2:$C$500, $B$2:$B$500, $J2, $A$2:$A$500, ">="&K$1, $A$2:$A$500, "<"&EDATE(K$1,1))
Fill it right across to M and down as far as your merchant list goes. The dollar signs lock the data ranges and the merchant column, while the month reference changes as you fill across. EDATE returns the same day one month later, so "less than EDATE(K$1,1)" captures every day up to the last of the month, including times on the final day if your data has them.
What would the finished grid show?
| Merchant | Sep | Oct | Nov | Total |
|---|---|---|---|---|
| Hall cleaning | £120.00 | £120.00 | £135.00 | £375.00 |
| Electricity top-up | £84.50 | £91.20 | £118.75 | £294.45 |
| Tea, milk and supplies | £23.40 | £31.85 | £28.10 | £83.35 |
The September column totals 120.00 + 84.50 + 23.40 = £227.90, October 120.00 + 91.20 + 31.85 = £243.05 and November 135.00 + 118.75 + 28.10 = £281.85. Added together, 227.90 + 243.05 + 281.85 = £752.80, which equals 375.00 + 294.45 + 83.35 = £752.80. These amounts are made up for the example.
How do I know nothing has been missed?
Add a check cell below the grid. For September, =SUMIFS($C$2:$C$500, $A$2:$A$500, ">="&K$1, $A$2:$A$500, "<"&EDATE(K$1,1)) - SUM(K2:K4) should return 0. If it does not, a transaction belongs to a merchant not in your list. A catch-all row labelled Other, computed the same way as that check, keeps the grid honest.
Can the totals row and column be formulas too?
Yes. For the right-hand column, =SUM(K2:M2) in N2, filled down. For the bottom row, =SUM(K2:K4), filled across. Resist typing totals by hand; a pasted number will not change when the data does.
When would a tool do the grouping for me?
If the manual upkeep of the merchant list becomes the chore, SpendixAI has a report builder that groups and filters, lets you save a report and exports it to CSV. It is built around India and INR, so check whether it suits you; the formula grid above works with any bank's CSV.
Extending the grid to a full year
To cover twelve months, type the first month in K1 and then make L1 =EDATE(K1,1), filling right to V1. The merchant formulas need no change. For the merchant list, sort a copy of the data by merchant and remove duplicates by hand, or use Remove Duplicates on a copy of the merchant column. When a new supplier appears, add one row at the bottom of the list and fill the formulas down. The grid then absorbs it without any other editing, and last year's totals stay put.
A last look
- Sort the merchant list by total so the biggest costs come first.
- Add a conditional format to highlight any cell that is more than a third above its row average.
- Keep the raw data on a separate tab.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

