formula help please: to return MAX number of consecutive 1's in column when the table filtered

[aiki100]

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!

 

[DanteAmor]

Change your formula. Without quotes for the numbers:

=IF(HJ3="W",1,0)

Try subtotal function:

varios 02jun2023.xlsm
	HJ																																																													JS	JT
1	h1																																																													h1
2	h2																																																													h2	7
3	W																																																													1
4	W																																																													1
8	W																																																													1
9	W																																																													1
12	W																																																													1
16	W																																																													1
17	W																																																													1
sh

Cell Formulas
Range	Formula
JT2	JT2	=SUBTOTAL(102,JS3:JS17)
JS3:JS17	JS3	=IF(HJ3="W",1,0)

 

[DanteAmor]

varios 02jun2023.xlsm
	HJ																																																													JS	JT
1	h1																																																													h1
2	h2																																																													h2	7
3	W																																																													1
4	W																																																													1
8	W																																																													1
9	W																																																													1
10	W																																																													1
16	W																																																													1
17	W																																																													1
sh

Cell Formulas
Range	Formula
JT2	JT2	=SUBTOTAL(102,JS3:JS17)
JS3:JS17	JS3	=IF(HJ3="W",1,0)

Or do you want 3 consecutive results, highlighted in yellow, rows 8, 9 and 10?

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[aiki100]

varios 02jun2023.xlsm
	HJ																																																													JS	JT
1	h1																																																													h1
2	h2																																																													h2	7
3	W																																																													1
4	W																																																													1
8	W																																																													1
9	W																																																													1
10	W																																																													1
16	W																																																													1
17	W																																																													1
sh

Cell Formulas
Range	Formula
JT2	JT2	=SUBTOTAL(102,JS3:JS17)
JS3:JS17	JS3	=IF(HJ3="W",1,0)

Or do you want 3 consecutive results, highlighted in yellow, rows 8, 9 and 10?

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Thanks so much for the reply:
I don't know why I added quotes to the numbers, being as they are not text, but I changed that, thank you.

So now, in JS3 I have: =IF(HJ3="W",1,0)
This checks that we have a "W" in column HJ (which is successfully calculating W and L), and if so, returns a 1.


What's next I am a little unclear on please: the end result is to be able to have one cell return the MAX of the unbroken string of W's.

I used the JS3 formula to convert a W in HJ to a 1 to somehow help with the count.
If you think we can make the formula work without a conversion to 1's for the counting of consecutive "W", and just use the "W" in HJ, that is also great.

What I need is a formula to return the MAX number of consecutive "W" in Col HJ, that will function properly when the table is filtered. I don't need them highlighted, thanks, just the MAX of the largest number of consecutive W.

Thanks again for the help!

 

[DanteAmor]

What I need is a formula to return the MAX number of consecutive "W" in Col HJ, that will function properly when the table is filtered. I don't need them highlighted, thanks, just the MAX of the largest number of consecutive W.

That doesn't answer my question.

In my example I highlighted it in yellow just to make it clear that we have 3 consecutive cells.
So, since we don't have an example of yours, continuing with my example, what you want as a result is a 7 or a 3?

If my example is not clear or it is not what you want, then give an example of yours, with the filtered data and the result you want, even if it is obvious to you, you must explain how you got to that result.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​