Google Sheets - How to count streaks of non-zero numbers on one column-CodePudding

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.

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)

CodePudding user response：

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)))))

CodePudding user response：

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'")))