Count of successive 'N' in a row reverting pack to zero when 'Y' entered.

[jessrw]

I am working on attendance records - see below.

I have a column at the end that needs to count the successive 'N' attendance in a row (start counting at 1 for first recording of it) and then continue counting for each successive 'N' in that row. But then the counting needs to restart when a Y is entered in the row.

I can remove the Mode and Yearly Unattend columns if it makes the formula more practical.

Any suggestions for a formula?

 

[Cubist]

I can remove the Mode and Yearly Unattend columns if it makes the formula more practical.

It is easier if all of the "Attend" are together. Try:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3	Y	Y	N	N	N	N		0	0	1	2	3	4
4	Y	N	N	Y	N	Y		0	1	2	0	1	0
Sheet6

Cell Formulas
Range	Formula
K3:P4	K3	=SCAN(0,D3:I3,LAMBDA(a,b,IF(b="N",a+1,0)))
Dynamic array formulas.

 

[Cubist]

If you want to keep the other columns, here's one way:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Attend	Mode	Attend	Mode	Yearly Unattend	Attend	Mode	Attend	Mode
3	Y	Online	Y	Online	1	N	Online	N	Online		0	0	1	2
4	Y	Person	N	Person	2	N	Person	N	Person		0	1	2	3
Sheet6

Cell Formulas
Range	Formula
N3:Q4	N3	=SCAN(0,FILTER(D3:L3,D$2:L$2="Attend"),LAMBDA(a,b,IF(b="N",a+1,0)))
Dynamic array formulas.