Count of last consecutive repetitive values in a column

[JONVEN]

Hello Excel Masters,

I have a problem which I am stuck with.. I have checked the forum and could not find the answer, even though I am quite close with to get the answer with this formula (Count number of times a value appears in role??) I am still looking for suggestion.

So, let's say a have a column with values:
Y
N
Y
Y
Y
N
N
N
I need a formula which would count number of last (not MAX) Ns in a row,- in this case this would be 3

In case of:
Y
Y
Y
N
N
Y
The formula should return 0, because last value is not N

Also would be great if formula could still count a number of last Ns in a row if there's 0s or blanks after Ns. In case of:
Y
N
N
Y
N
N
N
0/Blank
0/Blank
The formula should return 3, because before 0s/Blanks there was 3 consecutive Ns.

Hope the question is clear and someone could help me.
Thanks in advance.

 

[JONVEN]

Ok, will try to explain here, copied a mini sheet below.
So, I want to analyze football matches data and get the number of consecutive matches a team did not have more than 1 goal in the first half and more than 3 goals in full match time.
In the cells B7:B36 and C7:C36 I manually enter the number of goals scored after each match, after the result is entered the cells B42:B71 and C42:C71 automatically updates whether there was 2 or more goals in first half and whether there was 4 or more goals in the full match. (Y-there was >=2/>=4 goals, N-there was <2/<4 goals, 0-the goal number is not entered yet)
Cells H2 and J2 automatically updated and shows the longest consecutive streak of matches where there were < 2 goals in first half and <4 goals in full match (in this case 6 and 4 respectively).
I need G2 and J2 cells to be updated as well and show the current streak of matches where there were < 2 goals in first half and <4 goals in full match, so in this case G2 should return 2, and J2 should return 1. Hope this is clear what I want to achieve

working file.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Team	Matches played	Total FH goals	Avg. FH goals	Total FT goals	Avg. FT goals	Current no FH15 in	Max no FH15 in	Max no FT35 in	Current no FT35 in
2	Team A	22	31	1.41	69	3.14		6	4
3
4
5		Team A
6		FH goals	FT goals
7	Match 1	0	1
8	Match 2	1	4
9	Match 3	1	3
10	Match 4	0	0
11	Match 5	4	7
12	Match 6	2	3
13	Match 7	2	2
14	Match 8	2	3
15	Match 9	0	3
16	Match 10	1	4
17	Match 11	1	3
18	Match 12	0	0
19	Match 13	0	2
20	Match 14	0	1
21	Match 15	2	5
22	Match 16	1	3
23	Match 17	4	5
24	Match 18	1	1
25	Match 19	4	5
26	Match 20	3	5
27	Match 21	1	6
28	Match 22	1	3
29	Match 23
30	Match 24
31	Match 25
32	Match 26
33	Match 27
34	Match 28
35	Match 29
36	Match 30
37
38
39
40		Team A
41		FH goals	FT goals
42	Match 1	N	N
43	Match 2	N	Y
44	Match 3	N	N
45	Match 4	N	N
46	Match 5	Y	Y
47	Match 6	Y	N
48	Match 7	Y	N
49	Match 8	Y	N
50	Match 9	N	N
51	Match 10	N	Y
52	Match 11	N	N
53	Match 12	N	N
54	Match 13	N	N
55	Match 14	N	N
56	Match 15	Y	Y
57	Match 16	N	N
58	Match 17	Y	Y
59	Match 18	N	N
60	Match 19	Y	Y
61	Match 20	Y	Y
62	Match 21	N	Y
63	Match 22	N	N
64	Match 23	0	0
65	Match 24	0	0
66	Match 25	0	0
67	Match 26	0	0
68	Match 27	0	0
69	Match 28	0	0
70	Match 29	0	0
71	Match 30	0	0
Sheet3

Cell Formulas
Range	Formula
B2	B2	=COUNTA(B7:B36)
C2	C2	=SUM(B7:B36)
D2	D2	=C2/B2
E2	E2	=SUM(C7:C36)
F2	F2	=E2/B2
H2:I2	H2	=MAX(FREQUENCY(IF(B42:B71="N",IF(B42:B71<>"",ROW(B42:B71))),IF(B42:B71<>"N",IF(B42:B71<>"",ROW(B42:B71)))))
B42:B71	B42	=IF(ISBLANK(B7),0,IF(B7>=2,"Y","N"))
C42:C71	C42	=IF(ISBLANK(C7),0,IF(C7>=4,"Y","N"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Thanks for that, how about

=COUNTIF(B42:B71,"<>0")-LOOKUP(2,1/((B42:B71<>"N")*(B42:B71<>0)),ROW(B42:B71)-ROW(B42)+1)

 

[JONVEN]

Seems to be working perfectly, huge thanks

 

[Fluff]

Glad to help & thanks for the feedback.