Count 0s and 1s in streak - Habit Tracker

[gtcpenn]

Hi all,

I am building a habit tracker at the moment and I wanted to find a formula to count the streak (current consecutive number of successes) of following the habit. I also want to not count certain letters ("w", "o", and "h") which should not break the streak. 1s are days which I achieved my goal, 0s are when I have failed.

In short, what I want to do is have the current streak showing in B2:B7 per habit for the whole year

Have been struggling with this for days now. What I'm looking for is a formula if possible!

Thanks so much!!

George

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Habit	Current Streak
2			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	Workout	1	w	w	h	1	0	1	0	w	w	o	0	1	1	0	w	w	o	o	o	1		w	w						w	w
4	Cold Shower	5	w	w	h	0	0	1	1	w	w	0	1	1	1	1	w	w	o	o	o	1		w	w						w	w
5	Meditation	1	w	w	h	0	0	1	0	w	w	1	1	0	0	1	w	w	1	1	0	1		w	w						w	w
6	Vegan	0	1	1	1	1	1	o	0	1	1	1	1	1	1	1	1	1	1	1	1	0
7	Alcohol free	1	w	w	1	0	1	1	w	w	1	1	1	1	0	w	w	1	1	1	0	1	w	w						w	w
8	In Bed by 00	3	w	0	1	0	1	1	w	w	1	1	1	0	1	w	w	o	0	1	1	1	w	w
Sheet4

Cell Formulas
Range	Formula
B3:B8	B3	=COUNTIF(INDEX(C3:AG3,XMATCH(0,C3:AG3,1,-1)):AG3,1)

 

[gtcpenn]

Hi There,

Thanks for the response, and for the welcome!

Somehow when I put this into the file I end up with 0 as a streak - not sure why? Other than the range, is there something I need to adjust?

Cheers,

George

 

[Eric W]

Well, that's odd! I had a parameter wrong in the XMATCH function, I don't know why it worked at all on my test sheet. I also added a tweak so that it would work if the row has no 0 values. Give this a try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Habit	Current Streak
2			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	Workout	1	w	w	h	1	0	1	0	w	w	o	0	1	1	0	w	w	o	o	o	1		w	w						w	w
4	Cold Shower	5	w	w	h	0	0	1	1	w	w	0	1	1	1	1	w	w	o	o	o	1		w	w						w	w
5	Meditation	1	w	w	h	0	0	1	0	w	w	1	1	0	0	1	w	w	1	1	0	1		w	w						w	w
6	Vegan	0	1	1	1	1	1	o	0	1	1	1	1	1	1	1	1	1	1	1	1	0
7	Alcohol free	1	w	w	1	0	1	1	w	w	1	1	1	1	0	w	w	1	1	1	0	1	w	w						w	w
8	In Bed by 00	3	w	0	1	0	1	1	w	w	1	1	1	0	1	w	w	o	0	1	1	1	w	w
Sheet4

Cell Formulas
Range	Formula
B3:B8	B3	=COUNTIF(INDEX(C3:AG3,IFERROR(XMATCH(0,C3:AG3,0,-1),1)):AG3,1)

 

[gtcpenn]

Thanks so much for taking the time on this - you are amazing! This works well with the ws and os, but for it doesn't end the streak for me when I enter a zero anymore.

 

[Eric W]

I'm not sure what to tell you, a 0 restarts the streak when I test. Are there some cells formatted as text? There's an example in my last post even (row 6). If you could post an example, I'll take a look. I used a tool called XL2BB (see my signature) which is very easy to use and see exactly what's going on. But even a picture like you did before might help.