# Count consecutive columns with the same values?

Before I get started, here is a link to the sheet example: Essentially what I want to do is Count the number of times the value in C5 occurs consecutively in its first occurrence.

• In the example on Sheet1, the value would be 5 because Hawaii occurs 5 in a row before changing values.
• In the example on Sheet2, the value would be 2 because of Air Force occurs 2 in a row before changing values

I count use the `Countif()` but I only want the consecutive count for the first instance.

Any tips would be appreciated.

paste this into F5 (for example):

``````=MAX(ArrayFormula(IF(C5:C12=A2,
MMULT(N(ROW(C5:C12)>=TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2))
-
HLOOKUP(0,
MMULT(N(ROW(C5:C12)>TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2)),
MATCH(VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
0),FALSE),)))
``````

or sentence:

``````=A2&" won "&MAX(ArrayFormula(IF(C5:C12=A2,
MMULT(N(ROW(C5:C12)>=TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2))
-
HLOOKUP(0,
MMULT(N(ROW(C5:C12)>TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2)),
MATCH(VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
0),FALSE),)))&" times in a row"
``````