I'm trying to create a counter of the maximum daily "win streak" of a specific activity. However, in any given day it's perfectly reasonable to have not attempted the activity in question at all. Weekends in particular are the usual culprit. So I need to be able to count the largest "winning streak" that can have days which are skipped.

Data example:

Date Win
Mon, Sep 12 FALSE
Tue, Sep 13 TRUE
Wed, Sep 14 FALSE
Thu, Sep 15 TRUE
Fri, Sep 16 TRUE
Sat, Sep 17 -
Sun, Sep 18 -
Mon, Sep 19 TRUE

So in this example, the largest win streak is 3. Saturday and Sunday are ignored as nothing happened those days.

I currently am using a formula at this page and it works but does not ignore days without activity:


How can I modify this formula to ignore "null" days, or use a different single-formula (no helper columns etc.) approach?

1 Answer 1


Use frequency(), like this:

=arrayformula( max( frequency(if(B2:B = true, row(B2:B)), if(B2:B = false, row(B2:B))) ) )


Your Answer

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

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