Habit tracker in Excel: the streak formula and COUNTIF

Set up the habit grid

Put the dates in column A, one row per day, and give every habit its own column. Type a mark when the habit is done. The example uses three states:

• x = done
• - = a planned rest day (useful for workouts)
• empty = missed

Days done per habit is a plain count of the x marks: =COUNTIF(B2:B15,"x") returns 12 for reading and 8 for the workout. Do not use COUNTA here. It counts every cell that is not empty, so the workout column shows 12 because the four rest days count as done. A Data Validation list with the source x,- keeps the marks clean, because COUNTIF ignores X with a trailing space or a tick symbol.

The streak formula

Excel has no STREAK function, so a helper column does the work. The first day starts the count, every later day builds on the row above:

• D2: =IF(B2="x",1,0)
• D3: =IF(B3="x",D2+1,0), filled down to D15

The reference to D2 is relative on purpose: in row 10 the formula reads D9, in row 15 it reads D14. In the example the streak climbs to 4 by 5 March, drops to 0 on the missed 6 March, runs 1 to 5 from 7 to 11 March, resets on 12 March and stands at 3 on 15 March. If you want one formula for the whole column, including the first row under the header, use =IF(B2="x",N(D1)+1,0): N turns the header text into 0.

Worked example: a two-week habit tracker with a streak counter

Two weeks of fictional habit marks from 2 to 15 March 2026 (yellow inputs): x = done, - = planned rest day, empty = missed. Column D is the running streak for reading. Rows 17 to 22 count the done days, show the COUNTA trap, the success rates without rest days and the current and longest streak.

Worked example: a two-week habit tracker with a streak counter

	A	B	C	D
1	Date	Read 20 min	Workout	Read streak
2	02 Mar 2026	x	x	1
3	03 Mar 2026	x	-	2
4	04 Mar 2026	x	x	3
5	05 Mar 2026	x		4
6	06 Mar 2026		x	0
7	07 Mar 2026	x	-	1
8	08 Mar 2026	x	x	2
9	09 Mar 2026	x	x	3
10	10 Mar 2026	x	-	4
11	11 Mar 2026	x	x	5
12	12 Mar 2026			0
13	13 Mar 2026	x	x	1
14	14 Mar 2026	x	-	2
15	15 Mar 2026	x	x	3
16
17	Days done	12	8
18	Wrong: COUNTA (counts rest days)		12
19	Success rate (rest days excluded)	85.7%	80.0%
20	Current Read streak (last day)			3
21	Longest Read streak			5
22	Current streak without a helper column			3

Fictional data. In Excel, add a Data Validation list with the source x,- to the grid so only valid marks can be typed.

Current streak and longest streak

The longest streak is the largest value anywhere in the helper column: =MAX(D2:D15) = 5. The current streak is the value on the most recent day, here =D15 = 3. Taking the last row only works when the grid ends today. If you prepare the whole month in advance, the empty future days count as missed and the last row shows 0. Read the streak on today's row instead: =INDEX(D2:D32,MATCH(TODAY(),A2:A32,0)).

Without a helper column, the current streak is the number of days since the last day that is not an x: =ROWS(B2:B15)-SUMPRODUCT(MAX((B2:B15<>"x")*(ROW(B2:B15)-1))). The array finds the position of the last miss (day 11, 12 March), and 14 − 11 = 3. It is shorter to show on a dashboard, but the helper column is easier to check and also gives you the longest streak.

Rest days and the success rate

A planned rest day is neither a success nor a failure, so leave it out of the denominator: =C17/(ROWS(C2:C15)-COUNTIF(C2:C15,"-")). The workout was done on 8 of the 10 days that were not rest days, a success rate of 80.0%. Reading has no rest days, so it is 12 of 14, or 85.7%.

Rest days also matter for streaks. With the plain streak formula, a rest day resets the workout streak to 0. If the streak should pause instead, carry yesterday's value through: =IF(C3="x",E2+1,IF(C3="-",E2,0)). Only an empty cell, a real miss, then breaks the streak.

Green ticks with conditional formatting

Select the grid, choose Home > Conditional Formatting > Highlight Cells Rules > Equal To, type x and pick a green fill. Gaps now stand out without reading a single mark. A formula rule does the same and can colour the whole row: =$B2="x". For more patterns, such as highlighting today's row with =$A2=TODAY(), see the conditional formatting formula examples.

Prefer real tick boxes? In Microsoft 365 and Google Sheets, Insert > Checkbox stores TRUE or FALSE in the cell. Then count with =COUNTIF(B2:B15,TRUE) and build the streak with =IF(B3,D2+1,0).

Several habits and weekly totals

Give each habit its own streak column next to the grid, or place the streak columns on the right of all the habits so the grid stays compact. For a weekly review, count the done days between two dates with COUNTIFS: =COUNTIFS(A2:A15,">="&F2,A2:A15,"<"&F2+7,B2:B15,"x"), where F2 holds the Monday of the week. The two date conditions work exactly like the windows in the SUMIFS examples.

Common mistakes

• COUNTA instead of COUNTIF. COUNTA counts every non-empty cell, so rest days marked - count as done: 12 workouts instead of 8.
• A locked streak reference. =IF(B3="x",$D$2+1,0) always adds 1 to the first day, so the streak never passes 2. The reference to the row above must stay relative.
• The last row as the longest streak. The last row is the current streak (3). The longest streak is the maximum of the whole column (5).
• Inconsistent marks. x, X, a tick symbol and x with a trailing space look alike, but only the first two match "x". Use a drop-down list or checkboxes.
• A month prepared in advance. Empty future days look like misses and set the current streak to 0. Read the streak on today's row with INDEX and MATCH.
• Rest days in the denominator. Dividing 8 workouts by 14 days gives 57%. Planned rest days are not misses, so divide by the 10 training days.