Count of last consecutive repetitive values in a column

JONVEN

New Member
Joined
Sep 21, 2022
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2022
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJ
1TeamMatches playedTotal FH goalsAvg. FH goalsTotal FT goalsAvg. FT goalsCurrent no FH15 inMax no FH15 inMax no FT35 inCurrent no FT35 in
2Team A22311.41693.1464
3
4
5Team A
6FH goalsFT goals
7Match 101
8Match 214
9Match 313
10Match 400
11Match 547
12Match 623
13Match 722
14Match 823
15Match 903
16Match 1014
17Match 1113
18Match 1200
19Match 1302
20Match 1401
21Match 1525
22Match 1613
23Match 1745
24Match 1811
25Match 1945
26Match 2035
27Match 2116
28Match 2213
29Match 23
30Match 24
31Match 25
32Match 26
33Match 27
34Match 28
35Match 29
36Match 30
37
38
39
40Team A
41FH goalsFT goals
42Match 1NN
43Match 2NY
44Match 3NN
45Match 4NN
46Match 5YY
47Match 6YN
48Match 7YN
49Match 8YN
50Match 9NN
51Match 10NY
52Match 11NN
53Match 12NN
54Match 13NN
55Match 14NN
56Match 15YY
57Match 16NN
58Match 17YY
59Match 18NN
60Match 19YY
61Match 20YY
62Match 21NY
63Match 22NN
64Match 2300
65Match 2400
66Match 2500
67Match 2600
68Match 2700
69Match 2800
70Match 2900
71Match 3000
Sheet3
Cell Formulas
RangeFormula
B2B2=COUNTA(B7:B36)
C2C2=SUM(B7:B36)
D2D2=C2/B2
E2E2=SUM(C7:C36)
F2F2=E2/B2
H2:I2H2=MAX(FREQUENCY(IF(B42:B71="N",IF(B42:B71<>"",ROW(B42:B71))),IF(B42:B71<>"N",IF(B42:B71<>"",ROW(B42:B71)))))
B42:B71B42=IF(ISBLANK(B7),0,IF(B7>=2,"Y","N"))
C42:C71C42=IF(ISBLANK(C7),0,IF(C7>=4,"Y","N"))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,439
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
Excel Formula:
=COUNTIF(B42:B71,"<>0")-LOOKUP(2,1/((B42:B71<>"N")*(B42:B71<>0)),ROW(B42:B71)-ROW(B42)+1)
 
Solution

JONVEN

New Member
Joined
Sep 21, 2022
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Seems to be working perfectly, huge thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,439
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,186,178
Messages
5,956,392
Members
438,249
Latest member
georgebasalic3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top