STREAK

[lrobbo314]

STREAK will look at a range, and given a value, will return how many consecutive times the value appears in the range.

The function can look at a single row, single column, or range with multiple rows and columns.

=LAMBDA(range,lookup_value,by_row,
    LET(t,range,
    rc,IF(by_row,COLUMN(t),ROW(t)),
        IF(by_row,
            BYROW(t,LAMBDA(x,MAX(FREQUENCY(IF(x=lookup_value,rc),IF(x<>lookup_value,rc))))),
            BYCOL(t,LAMBDA(x,MAX(FREQUENCY(IF(x=lookup_value,rc),IF(x<>lookup_value,rc)))))
        )
    )
)

LAMBDA
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	B	A	B	A	C	C	C	A	C	C	A	A	B	C	C	A	B	A	B	2
2	A	C	A	B	A	A	A	A	A	B	B	A	A	C	A	C	C	A	A	5
3	A	C	B	A	B	A	B	A	B	A	A	B	B	B	A	A	B	C	A	2
4	B	B	C	C	A	A	A	A	B	B	A	C	B	A	B	C	A	C	C	4
5	C	C	A	A	B	C	C	A	C	B	A	A	C	B	C	B	C	B	B	2
6	A	A	A	A	A	C	C	B	B	A	A	B	C	C	C	C	A	A	B	5
7	B	A	B	B	C	A	B	B	A	C	A	B	B	A	C	A	C	A	C	1
8	A	B	A	C	C	C	C	C	A	A	A	C	B	C	A	B	A	A	A	3
9	A	B	A	C	B	B	B	B	C	B	C	C	A	B	C	A	A	B	C	2
10	B	B	C	C	A	B	A	A	C	C	B	C	A	C	B	C	C	B	A	2
11	A	B	B	C	B	B	C	B	C	A	B	B	C	C	C	B	C	A	A	2
12	A	A	A	C	B	C	C	A	B	A	B	A	A	A	A	A	A	B	A	6
13	2	2	2	2	1	3	1	5	2	2	6	2	2	1	2	1	2	3	3
14
15	Single Row
16	5
17
18	Single Column
19	6
Sheet2

Cell Formulas
Range	Formula
T1:T12	T1	=STREAK(A1:S12,"A",1)
A13:S13	A13	=STREAK(A1:S12,"A",0)
A16	A16	=STREAK(A6:S6,"A",1)
A19	A19	=STREAK(K1:K12,"A",0)
Dynamic array formulas.

 

[lrobbo314]

@Xlambda posted a cool alternative to this function. Looking at that formula gave me some insight on how I originally wanted to do this STREAK formula. Now I see how. Cheers Xlambda!

=LAMBDA(range,lookup_value,[by_row],
    LET(
        rc,IF(by_row,ROW(range),COLUMN(range)),
        m,LAMBDA(x,MAX(FREQUENCY(IF(x=lookup_value,rc),IF(x<>lookup_value,rc)))),
            IF(by_row,
                BYCOL(range,LAMBDA(b,m(b))),
                BYROW(range,LAMBDA(b,m(b)))
            )
    )
)