'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 :)
Solution 1:[1]
=MAX(FREQUENCY(IF(A1:A9,ROW(A1:A9)),IF(A1:A9=0,ROW(A1:A9))))
Solution 2:[2]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Jos Woolley |
| Solution 2 | navafolk |