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.

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.

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



