Below is a proposed solution for challenge Solution – Challenge 17 – Sum if Y Appears More than One Time Consecutively Maximum
1. Introduce a helper column in column D and put following formula in D2 and drag down –
=IF(B2="N",0,IF(AND(A2=$F$2,A1=A2),1+D1,1))
2. Put following formula in F3
=IF(ISNUMBER(MATCH(F2,A:A,0)),SUM(OFFSET(C1,MATCH(MAX(D2:D22),D2:D22,0),
0,-MAX(D2:D22),1)),"")