How I built a subscription tracker in Google Sheets (formulas to normalize any billing cycle)

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.

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