# Count consecutive columns with the same values?

Before I start, here is a link to the sheet example:

spreadsheet sample 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 using `Countif()` but I only want the consecutive count for the first instance.

Any tips would be appreciated.

## 1 Answer

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"
``````