CODEWITHSUNDEEP
excelexcel-formula
KingLouie
KingLouie

Reputation: 451

Excel - How to count streaks of non-zero numbers on one column

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.

Daily session count
100
200
400
100
200
=IF(C9<>"",SUM(C9-C8),"")
200
300
=IF(C9<>"",SUM(C9-C8),"")
=IF(C9<>"",SUM(C9-C8),"")
=IF(C9<>"",SUM(C9-C8),"")
=IF(C9<>"",SUM(C9-C8),"")

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

Edit: So the cells with 0 actually have a formula in them (meaning they are not empty) however they are only populated when the adjacent cell is updated with a daily wordcount, and so the daily session count is updated.

If I've missed a day of writing I still add the daily word count which means the daily session count is "0". Again, what I'm looking for is to count the cells in a column, as the longest streak of integers above 0. Thanks :)

Upvotes: 2

Views: 342

Answers (3)

JvdV
JvdV

Reputation: 75990

With ms365, try:

enter image description here

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

Upvotes: 5

navafolk
navafolk

Reputation: 127

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.

Upvotes: 0

Jos Woolley
Jos Woolley

Reputation: 9062

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

Upvotes: 1

Related Questions