The Excel file related to this problem can be downloaded from Challenge – Most Consecutive Appearance
Suppose you have a table like below. The challenge before you is to find the sum of prize money if Y appears consecutively more than once for a person maximum time. You need to input name in F2 and sum of prize money should appear in F3. If name doesn't meet the criterion of consecutive Y maximum times, then Prize money should be blanks or any message can be put here.
Taking the example of Smith, most consecutive times Y appears in rows 7 to 10. For Doe, rows 15 to 16 and for Liz 19 to 21. Note for Smith, Y appears consecutively 3 times, first time in rows 2 and 3, second time in rows 7 to 10 and third time in rows 12 to 13. But since, we are talking about maximum consecutive appearance, hence we need to consider row 7 to 10.
If their are ties, pick up the first maximum consecutive appearance.
Note – Without the use of a helper column, it may be very difficult to solve. Hence, please feel free to use a helper column. Maximum allowed helper column is 0.
Note – You may like to post your answer to this in comments.
With the use of helper column, I am able to solve it.
Assuming helper column is D, give this formula at D2 and copy it down.
=IF(AND(A1=A2,B1="Y",B2="Y"),SUM(D1,1),IF(B2="Y",1,0))
Then give this formula at F2 to get the result.
=INDEX($A$2:$A$22,MATCH(MAX($D$2:$D$22),$D$2:$D$22,0))
In continuation to my earlier comment, to calculate sum of Prize money give this formula at F3
=SUM(OFFSET(C1,SUMPRODUCT((A2:A22=F2)*(B2:B22="Y")*(D2:D22=MAX($D$2:$D$22))*ROW(A2:A22))-1,0,-MAX($D$2:$D$22)))