Count the maximum consecutive TRUEs

Originally Posted by MarvinP

Hi tomgilb and welcome to the forum,

See if the attached helps. I've used Column D for the helper column with the answer in D1.

Thanks Marvin,

The formula =IF(B3=TRUE,D2+1,0) you offered increments the value, but it doesn't count the consecutive TRUEs n rows back (e.g. 6 rows, row 3 thru row 8). It also increments in ascending order, and the timestamps (worksheet) are in descending order.

I've tried this in cell D3:
=IF(AND(B3=TRUE,D4<$D$1),B3+D4,0)
It increments the consecutive TRUE count within the variable length n but fails to maintain the count for n rows back. This is my starting formula, and I've tried many iterations without success. This one is a brain twister for me.