Count MAX consecutive values with multiple criteria

Question

I want to get max consecutive values to count with multiple criteria. I found this formula online

=ArrayFormula(MAX(FREQUENCY(IF($B$2:$B="WIN",ROW($B$2:$B)),IF($B$2:$B<>"WIN",ROW($B$2:$B)))))

and it works great for one criterion, counting "win" and/or "buy" by itself. But I want to get max consecutive count when 2 criteria are met at the same time, sort of cross-reference.

Like in the above example I want a max consecutive count of "buy" which by itself is 5, and "win" which by itself is 4. But I want the max consecutive count of when they occur together, which is only 2 times in the above example.

I tried modifying this formula in different ways but never got the correct result.

Here's sample doc.

Answer 1

You have to combine the conditions for "Buy" and "Win" with a multiplication in the array formula to get an AND. Then you need to negate that expression in the second part of the FREQUENCY. You could either use NOT with the same expression:

=ArrayFormula(MAX(FREQUENCY(IF((A2:A="Buy")*($B$2:$B="WIN"),ROW($B$2:$B)),IF(not((A2:A="Buy")*($B$2:$B="WIN")),ROW($B$2:$B)))))

or use a bit of Boolean logic to convert it to an OR expression using addition as below:

=ArrayFormula(MAX(FREQUENCY(IF((A2:A="Buy")*($B$2:$B="WIN"),ROW($B$2:$B)),IF((A2:A<>"Buy")+($B$2:$B<>"WIN"),ROW($B$2:$B)))))