Excel - 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 use this for Google Sheets but I need it to work for EXCEL.

=INDEX(COLUMNS(SPLIT(FLATTEN(SPLIT(TRIM(QUERY('Daily Count'!B2:B,,9^9)), " 0 ", )), " ")))

CodePudding user response：

=MAX(FREQUENCY(IF(A1:A9,ROW(A1:A9)),IF(A1:A9=0,ROW(A1:A9))))

CodePudding user response：

With ms365, try:

Formula in B1:

=MAX(SCAN(0,A1:A9,LAMBDA(a,b,(a (b<>0))*(b<>0))))

Or, with LET() nested:

=MAX(SCAN(0,A1:A9,LAMBDA(a,b,LET(x,b<>0,(a x)*x))))

CodePudding user response：

You can try this in excel:

=MAX(FREQUENCY(IF(A1:A9<>0,ROW(A1:A9)),IF(A1:A9=0,ROW(A1:A9))))

This is an array formula, so please hit Ctrl Shift Enter

A1:A9 is assumed your range.