Hi,

Perhaps try:

Sheet1

A B C D 1 List Value Max consecutive count 2 yes no 10 3 yes 4 yes 5 yes 6 no 7 no 8 no 9 yes 10 no 11 no 12 no 13 no 14 no 15 no 16 no 17 no 18 no 19 no 20 yes 21 yes Excel 2010Note:

Array Formulas

Cell Formula D2 =MAX(FREQUENCY(

IF(A2:A21=C2,ROW(A2:A21)),

IF(A2:A21<>C2,ROW(A2:A21))))Entered with Ctrl+Shift+Enter.If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter these manually yourself

- This method is described by Domenic here: Count Consecutive Numbers
- Other similar implementations include:

- Count singles,double,tripple: http://www.mrexcel.com/forum/excel-q...e-tripple.html
- Start Position Of Most Consecutive 1s in Column: http://www.mrexcel.com/forum/excel-q...1s-column.html
- Formula to count how many times adjacent cells in a row fulfil a certain criteria: http://www.mrexcel.com/forum/excel-q...ml#post3256085

## Like this thread? Share it with others