The test log has four passes. The useful question today is how many happened in a row. Make a helper column that continues only while the results say Pass.

Seed the first result and fill down the running count

Enter Pass, Pass, Fail, Pass, a blank, and Pass in B2:B7. In C2 enter =IF(B2="Pass",1,0). In C3 enter:

=IF(B3="Pass",C2+1,0)

Fill through C7. The streaks are 1, 2, 0, 1, 0 and 1. Here a blank breaks the run. Remove layout-only spacer rows from a working copy if they should have no effect.

Pass, Pass, Fail, Pass, blank and Pass produce streak lengths 1, 2, 0, 1, 0 and 1.

Illustrated running count. The blank represents a break in this example.

Read the latest value and the maximum separately

C7 gives the current streak of 1. Use =MAX(C2:C7) for the longest streak of 2. Sort by actual test order first. If several stations share a worksheet, calculate runs by station so one station’s result cannot continue another’s streak.

The latest result has streak 1, and the maximum streak is 2.

Illustrated result check. Keep the tests in their actual order.