Count consecutive instances of a particular value

Hi Stacey,

That's an interesting challenge. Here's my approach:

There are two Header Rows, even though I made the formatting change to make the second header row look like a body row for consistency with the rest of the data. The reason I did that is so that you can expand the table and have the new rows automatically fill with the formulas of the body rows. Adding rows when all body rows are the same causes the autofill behavior. I needed the first data row to have a different equation, so I used a header for the first row of data.

So, here are the equations:

B2: =IF(B="Right", 1, 0)

B3 to the last body row: =IF(B="Right", C2+1, 0)

Column B's first Footer row:

=COUNTIF(B, "Right")+COUNTIF(B2, "Right")

Column B's second Footer row:

=COUNTIF(B, "Wrong")+COUNTIF(B2, "Wrong")

Column B's third Footer row:

=MAX(C, C2)

That's it.

Regards,

Jerry

Good solution, Jerry. Here's a minor variation that does away with the necessity to include two header rows, and with the complications added to the formulas due to that inclusion.

Row 1 is a header row, rows 33, 34 and 35 are footer rows.

Formulas are essentially the same as Jerry's. B35 and C2 (filled down) are revised:

B33: =COUNTIF(B, "Right")

B34: =COUNTIF(B, "Wrong")

B35: =MAX(C)

C2 and filled down: =IF(B="Right", C1+1, 0)

C1 must contain the number "0"

Regards,

Barry