Hello all -
In Excel O365, I have a table that has data which starts in row3. (Row 1 and 2 are headers)
In col HJ (starting with HJ3), I have formulas that return "W", "L", or "". (So Win, Loss, or Blank)
In col JS, I have formulas that check if HJ (is a "w". If it is a "w" the formula returns a "1". (So in col JS, every 1= a "W"" that appears in col HJ)
Here is the formula for JS: =IF(HJ3="W","1","0"). I copy this formula down.
Now, when I filter the table, I can visually see where there are multiple 1's in a row, indicating a string of consecutive "W".
What I need is a formula to return the MAX number of consecutive 1's in JS when the table filtered.
example: So if I filter the table and can visibly see that the largest number of consecutive 1's in the filtered col JS is 7, then this formula should return 7.
I have tried various versions of SUBTOTAL and FREQUENCY (arrays), etc. but can't seem to get it.
Any help much appreciated, thank you!
In Excel O365, I have a table that has data which starts in row3. (Row 1 and 2 are headers)
In col HJ (starting with HJ3), I have formulas that return "W", "L", or "". (So Win, Loss, or Blank)
In col JS, I have formulas that check if HJ (is a "w". If it is a "w" the formula returns a "1". (So in col JS, every 1= a "W"" that appears in col HJ)
Here is the formula for JS: =IF(HJ3="W","1","0"). I copy this formula down.
Now, when I filter the table, I can visually see where there are multiple 1's in a row, indicating a string of consecutive "W".
What I need is a formula to return the MAX number of consecutive 1's in JS when the table filtered.
example: So if I filter the table and can visibly see that the largest number of consecutive 1's in the filtered col JS is 7, then this formula should return 7.
I have tried various versions of SUBTOTAL and FREQUENCY (arrays), etc. but can't seem to get it.
Any help much appreciated, thank you!