2

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

KingLouie
  • 399
  • 1
  • 5
  • 13
  • Basically the same question as https://stackoverflow.com/questions/70340291/repetitive-calculating-distance-between-two-values-excel/70422775#70422775 – Christopher Hamkins Mar 04 '22 at 12:37

3 Answers3

5

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))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • So this works, but I have formulas in the cells, so it is counting those. I need it to only count cells that have a number value in them. – KingLouie Mar 04 '22 at 13:07
  • 1
    @Kinglouie, can you update you question with a better sample data set. – JvdV Mar 04 '22 at 13:57
  • Hey @JvdV any chance you could update your answer? Thanks for taking the time to help me. – KingLouie Mar 06 '22 at 21:14
1

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

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
0

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.

navafolk
  • 103
  • 5