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

Maggie B — Mon, 26 Oct 2026 08:51:08 +0000

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

DEV Community: Maggie B