Spreadsheets

Find recurring subscriptions in your statement with a formula

Find recurring subscriptions in your statement sheet with COUNTIFS: flag a merchant and amount that repeats three months in a row, then total the monthly cost.

Editorial illustration for Find recurring subscriptions in your statement with a formula

To find recurring subscriptions in a statement sheet, add a month column, then use COUNTIFS to check whether the same merchant and amount also appear in each of the two previous months. A row that passes is flagged as repeating three months in a row, and a SUMIFS over the flagged rows gives your monthly subscription total.

What does the sheet need first?

Paste your debits into a sheet with Date in column A, Merchant in column B and Amount in column C, rows 2 to 500. Then add a helper in column D that turns each date into the first day of its month:

=DATE(YEAR(A2), MONTH(A2), 1)

Clean merchant names matter more than anything else here. A statement may show the same service under slightly different narrations from one month to the next, so make column B consistent first, for example by writing a short, standard name next to each line.

How do you flag a three-month repeat?

In E2, test whether the same merchant and the same amount appear in the month before and the month before that:

=IF(AND(COUNTIFS($B$2:$B$500, B2, $C$2:$C$500, C2, $D$2:$D$500, EDATE(D2,-1))>0, COUNTIFS($B$2:$B$500, B2, $C$2:$C$500, C2, $D$2:$D$500, EDATE(D2,-2))>0), "Repeats", "")

Fill it down. A row says Repeats only if the identical merchant and amount exist one month earlier and two months earlier. The third month in a run is the first row to be flagged, which is the point: two payments could be a coincidence and three in a row is a pattern.

How do you total what you pay each month?

Put the first day of the month you want to examine in H1, then in H2:

=SUMIFS($C$2:$C$500, $E$2:$E$500, "Repeats", $D$2:$D$500, H1)

This adds the flagged rows for that month. Say the flagged rows are a music service at ₹119, a video service at ₹199 and cloud storage at ₹130 (all invented figures). The total is 119 plus 199 plus 130, which is ₹448 a month, or ₹5,376 over twelve months. Seeing the yearly figure is often the surprise.

MerchantMonthly amountYearly cost
Music service₹119₹1,428
Video service₹199₹2,388
Cloud storage₹130₹1,560
Total₹448₹5,376

Which subscriptions will this method miss?

  • Charges whose amount changes slightly, such as a plan with a price revision or a charge in dollars that follows the exchange rate. The exact amount no longer matches.
  • Annual plans. A payment that comes once a year cannot repeat three months running. To catch these, look for the same merchant in the same month of the previous year with =COUNTIFS($B$2:$B$500, B2, $D$2:$D$500, EDATE(D2,-12))>0.
  • Quarterly bills, which need a three-month gap in the test instead of one.
  • Payments made through different accounts. Combine statements first, or you will see only one account's subscriptions.

If amounts drift, relax the test to merchant only, dropping the amount criterion, and read the amounts yourself. You will get more false positives, such as a grocery store, but fewer misses.

What should you do with the list?

  1. Write down what each flagged item is and whether you used it this month.
  2. Mark each one Keep, Review or Cancel in a column beside it.
  3. Set a calendar reminder for the date of the next renewal before you cancel anything with a notice period.
  4. Re-run the sheet after a month to confirm the charge stopped.

When your statements pile up, uploading them to SpendixAI and reviewing the rows before saving gives you a categorised history to scan, though spotting subscriptions will still be your own judgement. The formulas above keep that judgement quick.

SPENDIXAI JOURNAL

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

Explore your reports