Subscriptions are sneaky because they bill on different rhythms. A $139/year Prime membership, a $15.49/month Netflix plan and a $9/week meal kit can't be compared until you put them on the same scale. Here's how I built a tracker that does that automatically. It works the same way in Google Sheets and Excel.

1. The columns

One row per subscription on a Subscriptions sheet:

A B C D E F G H I J K
Service Category Cost Billing cycle Monthly cost Yearly cost Next renewal Days left Payment method Status Notes

You type into C, D, G and J. Columns E, F and H are formulas.

2. A lookup table instead of nested IFs

The tempting approach is IF(D2="Weekly",C2*52/12,IF(D2="Monthly",C2,...)). It breaks the moment you add a cycle. Instead, make a small Lists sheet:

Cycle (A) Payments per year (B)
Weekly 52
Monthly 12
Quarterly 4
Every 6 months 2
Yearly 1

Now every cycle is normalized the same way: cost × payments per year ÷ 12.

3. Monthly cost (E2) 

=IF(C2="","",IF(J2="Cancelled",0,C2*IFERROR(INDEX(Lists!$B$2:$B$6,MATCH(D2,Lists!$A$2:$A$6,0)),0)/12))

Reading it inside-out:

  • MATCH(D2, Lists!$A$2:$A$6, 0) finds the row of the billing cycle (exact match).
  • INDEX(Lists!$B$2:$B$6, …) returns payments per year.
  • IFERROR(…, 0) keeps a typo or a blank cycle from spraying #N/A across your totals.
  • IF(J2="Cancelled", 0, …) lets you keep cancelled rows for history without counting them.
  • The outer IF(C2="","",…) keeps empty rows blank.

4. Yearly cost (F2) 

=IF(E2="","",E2*12)

Once everything is monthly, yearly is trivial. This column is the one that hurts: seeing $14/month as $168/year changes decisions.

5. Days until renewal (H2) 

=IF(G2="","",G2-TODAY())

Dates are just numbers, so subtraction gives days. TODAY() recalculates on open, so the countdown is always current.

Add conditional formatting to make it pop: a rule on H2:H101 with a custom formula like =AND($H2>=0,$H2<=7) and a red fill. I also grey out cancelled rows with =$J2="Cancelled".

6. Data validation

Use dropdowns sourced from the Lists sheet (cycles in Lists!A2:A6, categories in D2:D11, statuses in E2:E5). This matters more than it looks, because the MATCH in step 3 needs exact text.

7. The dashboard

A separate Dashboard sheet just aggregates:

Monthly total: =SUM(Subscriptions!E2:E101) Yearly total: =SUM(Subscriptions!F2:F101) Active subscriptions: =COUNTIF(Subscriptions!J2:J101,"Active")+COUNTIF(Subscriptions!J2:J101,"Trial")+COUNTIF(Subscriptions!J2:J101,"Review") Renewing in next 7 days: =COUNTIFS(Subscriptions!H2:H101,">=0",Subscriptions!H2:H101,"<=7") Renewing in next 30 days: =COUNTIFS(Subscriptions!H2:H101,">=0",Subscriptions!H2:H101,"<=30") Marked for review ($/mo): =SUMIF(Subscriptions!J2:J101,"Review",Subscriptions!E2:E101)

And a per-category breakdown, with category names in column E:

=SUMIF(Subscriptions!B2:B101,E2,Subscriptions!E2:E101)

Feed that range into a pie chart and you can see where the money goes.

Dashboard showing monthly and yearly totals, renewal counts, and spend by category

8. Tips

  • The ">=0" condition in COUNTIFS excludes past dates, so stale renewals don't count as "upcoming". Update the date after each renewal.
  • A "Review" status is a gentle middle ground between Active and Cancelled. The dashboard tells you how much per month is riding on those decisions.
  • Excel and Sheets both handle every formula here with no scripts or add-ons.

Wrap-up

That's the whole thing: one lookup table, three formula columns and a summary sheet. Build it yourself in about 20 minutes with the formulas above. If you'd rather skip the setup, I packaged the finished version (Excel + Google Sheets, dashboard and chart included) for $1: https://ailstudio.gumroad.com/l/qkwkix