To find recurring payments, count in how many different months each cleaned merchant appears, and keep those that show up in three or more. COUNTIFS does the counting with two helper columns. Then compare the lowest and highest amount for each merchant, because a fixed amount points to a subscription while a varying one points to everyday shopping.
What do you need first?
A clean merchant column, because ROYAL SWEETS and ROYAL SWEETS CHENNAI count as two merchants otherwise. Put the date in A, the clean merchant in B and the amount in C. The sample below is invented and covers April to September.
Which helper columns do the work?
| Column | Formula in row 2 | What it does |
|---|---|---|
| D (month key) | =TEXT(A2,"yyyy-mm") | Turns a date into a label such as 2025-04 |
| E (first this month) | =IF(COUNTIFS($B$2:B2,B2,$D$2:D2,D2)=1,1,0) | Gives 1 the first time this merchant appears in that month |
| F (months seen) | =SUMIFS($E$2:$E$500,$B$2:$B$500,B2) | Adds the first-in-month flags, which is the number of distinct months |
The expanding range in column E is the important trick. $B$2:B2 grows as you fill down, so each row only counts earlier rows and itself. A merchant paid twice in April gets a 1 on the first payment and a 0 on the second, so April counts once. Column F then adds up the 1s for that merchant across the whole list.
What does it look like on six months of data?
Suppose the sheet gives these results after the helper columns run:
| Merchant | Months seen | Lowest | Highest | Reading |
|---|---|---|---|---|
| Cloud Notes | 6 | ₹199 | ₹199 | Fixed amount, every month |
| Anand Dairy | 6 | ₹1,450 | ₹2,100 | Every month but the amount varies |
| Fit Club | 3 | ₹1,500 | ₹1,500 | Fixed, started in July |
| Quick Cab | 2 | ₹180 | ₹340 | Not enough months |
The lowest and highest come from =MINIFS($C$2:$C$500,$B$2:$B$500,B2) and =MAXIFS($C$2:$C$500,$B$2:$B$500,B2). MINIFS and MAXIFS need Excel 2019 or Microsoft 365. In older versions, use a pivot with Min and Max in Values.
Filter column F for 3 or more. That keeps Cloud Notes, Anand Dairy and Fit Club. Cloud Notes alone is a clear subscription: ₹199 a month for six months is 199 × 6 = ₹1,194. If it carried on, a full year would be ₹2,388, which is a figure worth seeing.
Why are fixed amounts the clue?
A merchant seen every month is not automatically a subscription. Milk, fuel and groceries appear all the time. What separates a subscription is the same amount, usually on a similar day. Add a column for the spread, =MAXIFS(...)-MINIFS(...), and sort ascending among merchants with three or more months. A spread of zero is a strong sign of something fixed.
What should you do with the list?
- For each fixed-amount item, ask whether you still use it.
- Note the date of the month it usually leaves, so you know when to check.
- Check the month count again after next month's statement.
- If something is no longer wanted, cancel it with the provider, not just by blocking the payment.
Sort the final list by yearly cost rather than monthly cost. Cloud Notes at ₹199 a month is ₹2,388 a year, and Fit Club at ₹1,500 a month is ₹18,000 a year if it continues for twelve months. Seeing ₹18,000 beside ₹2,388 makes it clearer which one deserves a review first, even though the monthly figures look like small numbers.
Limits to keep in mind
- Yearly or quarterly payments will not appear in a six-month window.
- A change in the merchant spelling splits one subscription in two.
- Months with no statement in the file look like months without payments.
SpendixAI is another way to start, with statement import, a Possible duplicate charges report and, on Pro, budgets, bills and savings goals. The COUNTIFS method works anywhere you have a clean list.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


