Habit Tracker in Google Sheets: Streaks and % Done with a Few Formulas

Short answer: type an x, never a checkbox. Count done per day with COUNTIF, streaks with one helper column (=IF(E7="x",AD6+1,0)), and completion per week or month with COUNTIFS on the date column. Then judge each habit against its own weekly target, not against 100%: in the sample, the habit with the lowest completion (Workout, 43%) is the one that is exactly on plan.

The grid: one row per day, one x per habit

The Tracker tab has a date in column B and one column per habit (E to X, up to 20 habits). The habits themselves live on a separate Habits tab with a name, a target in days per week (7 = every day) and a start date. The sample has eight:

Note the Journal row. It started a month later, so it is counted from 1 August: 29 of 43 days. Counted from the first day of the tracker it would read 29 of 74, a misleading 39%. That is why each habit gets a start date, and why the daily "done" count only includes columns whose habit has started:

Y6:  =COUNTIFS(E6:X6, "x", $E$4:$X$4, "<="&B6)    ← row 4 holds each habit's start date

A plain =COUNTIF(E6:X6,"x") works too if all your habits start on the same day. Either way, the mark is a typed x, not a checkbox: checkboxes turn into TRUE/FALSE text or vanish when the file opens in Excel or a phone app, and an x counts the same everywhere.

Streaks: one helper column per habit

A streak is "days in a row with an x". The trick is to let every row carry the run forward. Next to the grid, one hidden column per habit:

AD6:  =IF(E6="x", 1, 0)
AD7:  =IF(E7="x", AD6+1, 0)        ← copy down 366 rows
Longest streak:  =MAX(AD6:AD371)

An x adds 1 to yesterday's value, a blank drops it to 0, and the longest run survives in the MAX. The current streak is the helper value on the last day you tracked anything. One refinement matters: if today is still blank because it is 8 am, look at yesterday instead, otherwise every streak shows 0 until you mark something.

In the sample, water is on its best run ever (23 days, which is also its longest), bedtime is on 12, and Meditate sits at 0 with a record of 7. The last-7-days strip shows why: two x marks on Monday and Tuesday, then nothing.

% of target, not % of days

Completion (days done divided by days since the start) ranks Workout last at 43%. But Workout is planned three times a week, and 3 out of 7 days is 42.9%. It is exactly where it should be. The fair number is done against what the target expects for the days so far, capped at 100%:

=MIN(1, done / (target_per_week * days_so_far / 7))

For September (1 to 12, 12 days) the dashboard shows:

Six of eight habits are at or near plan. The two that are not are the two with daily or near-daily targets that slipped (phone) or the one that is quietly dying (meditation). The month's count per habit is one COUNTIFS on the date column, the same pattern the annual budget uses for spending by month:

=COUNTIFS(Tracker!$E$6:$E$371, "x",
          Tracker!$B$6:$B$371, ">="&DATE(2026,9,1),
          Tracker!$B$6:$B$371, "<="&EOMONTH(DATE(2026,9,1),0))

The weekly review catches the slide early

Month totals hide direction. Four Monday-to-Sunday columns show it. The sample, weeks starting 17 August to 7 September (the last one is six days so far):

Meditation went 6, 5, 3, 2. In July and August it ran at 61% and 65% of days; in September it is at 4 of 12. The linked goal on the Goals tab ("90 days of meditation", target 75% completion) reads Behind at 58%, while "Sleep 7 hours every night" (linked to bedtime, target 75%) reads On track at 76%. A weekly number is the earliest place a slide like this shows up, three weeks before a monthly one would.

One more pattern the grid gives for free: by weekday, Monday is the best day in the sample (74% of habit-days done) and Saturday the worst (56%). If your weekend looks like that, a lower weekend target is more honest than a daily one you miss twice a week.

What to track, and what to skip

1. Five to eight habits. The sample's eight already make a perfect day rare: 3 in 74 days (3 July, 6 August, 7 September). Every habit you add makes that tile emptier.
2. Set a weekly target per habit. 7 for water, 3 for the gym. Without it the dashboard can only say "43%" and you will read it as failing.
3. A start date per habit. New habits count from their first day, not from January.
4. Keep the longest streak. A missed day resets the current one to 0; the record staying on screen is what makes day 1 of the next run tolerable.
5. Look at the week, not the day. Today at 38% (3 of 8 by the time the dashboard was captured) means nothing at noon. The week at 75% does.

Skip the build

The formulas above are the core. The template wraps them into 11 tabs where you type in two (Habits and Tracker): a dashboard with the tiles and two charts, the classic month grid filled from the Tracker, streaks, the 4-week review with trend, a 12-month year view with Q1 to Q4, goals linked to habits with On track / Behind, and a phone tab. There is also a 3-tab Lite file: habit names in the Tracker header, an x per day, a small dashboard.

See it work

FAQ