0

So, I have a Google Sheet with various readership/writing stats, and I'm using one column to log how many words I'm writing every day. I'd like to use that column to calculate writing streaks, i.e., have a cell that records the most cells in a row that have any value in them (days in which I wrote any amount of words) before being interrupted by a blank cell (day in which I didn't write). I've seen formulas for counting streaks of the same consecutive value, and formulas for counting non-empty cells, but for the life of me I can't figure out how to put them together in a formula that doesn't result in an error.

1
  • 1
    It would be best if you could share a copy of your spreadsheet.
    – ZygD
    Dec 15, 2021 at 9:09

1 Answer 1

1

Use this max(len(split())) pattern:

=arrayformula( 
  if( 
    len(join("", A2:A)), 
    max( len( split( 
      concatenate( left(isblank(A2:A)) ), 
      "T" 
    ) ) ), 
    "no streak" 
  ) 
)

The formula assumes that the number of words figures are in column A2:A.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.