assistance with max consecutive occurrences in a manipulable range, please

[Bab1]

Kind People,
I need assistance with a complex formula (for me at least) to count the max consecutive occurrences a a letter in a range. The range in which to search however' should be manipulable - if possible.
I attached a sample of the basic spreadsheet I am working on - where I point out where my problems lie.
Your kind assistance will be appreciated - as always.
Thank you kindly and regards.
Bab

[JeteMc]

Try this modification of your array entered formula* in C15:

Please Login or Register  to view this content.

*array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[JeteMc]

I think that I missed an important requirement, the count needs to fall within the range of dates specified. To accomplish that the following array entered formula, placed in B15 references two helper cells, which may be hidden for aesthetic purposes*:

Please Login or Register  to view this content.

The formulas to find the start and end of the period respectively are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

*These formulas could be incorporated into the array entered formula in cell B15, however I feel that it is easier to understand how the overall solution works if you are able to see their results. Let us know if you have any questions.

[Bab1]

JeteMc,
Really appreciated, thanks.
Your solution solved my problem. More so because it was made simple and clear.
Regards.
Bab

[Bab1]

JeteMc,
Sorry - back again:
In your initial array above; =MAX(FREQUENCY(IF(B3:AGZ3="P",COLUMN(B2:AGZ2)),IF(B3:AGZ3<>"P",COLUMN(B2:AGZ2)))), the range "searched" is B3:AGZ3 or basically in row 3.
When I use the more streamlined array; =MAX(FREQUENCY(IF(INDIRECT(B17&":"&B18)="P",COLUMN(INDIRECT(B17&":"&B18))),IF(INDIRECT(B17&":"&B18)<>"P",COLUMN(INDIRECT(B17&":"&B18))))), I naturally cannot see the range as in the above array, and have no idea how to change that to do a different range i.e. B4:AGZ4 or basically row 4.
How do I do this or how do I edit inside that array?
Thank you in advance.
Regards.
Bab

[Bab1]

JeteMc,
Sorry - back again:
In your initial array above; =MAX(FREQUENCY(IF(B3:AGZ3="P",COLUMN(B2:AGZ2)),IF(B3:AGZ3<>"P",COLUMN(B2:AGZ2)))), the range "searched" is B3:AGZ3 or basically in row 3.
When I use the more streamlined array; =MAX(FREQUENCY(IF(INDIRECT(B17&":"&B18)="P",COLUMN(INDIRECT(B17&":"&B18))),IF(INDIRECT(B17&":"&B18)<>"P",COLUMN(INDIRECT(B17&":"&B18))))), I naturally cannot see the range as in the above array, and have no idea how to change that to do a different range i.e. B4:AGZ4 or basically row 4.
How do I do this or how do I edit inside that array?
Thank you in advance.
Regards.
Bab

[JeteMc]

The change is made with the helper cells as in changing 3 to 4. I modified the helper cell formulas so that they find the letters A and B in column A, instead of having the row number 3 and 4 hard coded into the formula.
The formula for B17 now reads:

Please Login or Register  to view this content.

The formula for B18 now reads:

Please Login or Register  to view this content.

The formulas can be copied/pasted into K17:K18. Similarly the formula from B15 can be copied/pasted into K15 as is shown in the attached file.
Let us know if you have any questions.

[Bab1]

Works perfectly, thanks.

[JeteMc]

You're Welcome. Thank you for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.