I have a wordcount column with various numbers but each cell has a formula. So I'd like to count only the cells that have a non-zero number and stop when it hits a zero. A streak in other words.
100
200
400
100
200
0
200
300
0
the longest streak in this example would be 5. What formula could I use for this? I've tried this already, but it's looking for blank cells not cells with "0"
=ArrayFormula(match(TRUE,ISBLANK('Daily Count'!B2:B),0)-1)
try:
=ARRAYFORMULA(MMULT(SPLIT(FLATTEN(SPLIT(TRIM(QUERY(A1:A,,9^9)), " 0 ", )), " ")*1,
SEQUENCE(COLUMNS(SPLIT(FLATTEN(SPLIT(TRIM(QUERY(A1:A,,9^9)), " 0 ", )), " ")), 1, 1, )))
for longest strike use:
=INDEX(COLUMNS(SPLIT(FLATTEN(SPLIT(TRIM(QUERY(A1:A,,9^9)), " 0 ", )), " ")))
to list all strikes:
=ARRAYFORMULA(LEN(QUERY(SUBSTITUTE(FLATTEN(SPLIT(TRIM(QUERY(
IF(A1:A<>0, 1, 0),,9^9))&" ", " 0 ", )), " ", ), "where Col1 <> '0'")))
The classic way (same method works in Excel) is to use Frequency:
=ArrayFormula(max(frequency(if(B2:B>0,row(B2:B)),if(B2:B<=0,row(B2:B)))))
