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*:
Formula:
=MAX(FREQUENCY(IF(INDIRECT(B17&":"&B18)="P",COLUMN(INDIRECT(B17&":"&B18))),IF(INDIRECT(B17&":"&B18)<>"P",COLUMN(INDIRECT(B17&":"&B18)))))
The formulas to find the start and end of the period respectively are:
Formula:
=ADDRESS(3,MATCH(E8,B1:AGZ1,0)+1)
Formula:
=ADDRESS(3,MATCH(E9,B1:AGZ1,0)+1)
*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.
Bookmarks