Finding Maximum count of consecutive occurrances of a text from an range in excel.

Hi,

Can we have a formula for finding the maximum count in an excel range? For e.g If an employee has consecutively worked more than 6 days, then it should return 1 or else it should return 0.

In the below e.g. Let's assume that an employee has been present consecutively for 9 days. In this case the formula should return 1.

1-Mar 2-Mar 3-Mar 4-Mar 5-Mar 6-Mar 7-Mar 8-Mar 9-Mar 10-Mar 11-Mar 12-Mar 13-Mar 14-Mar 15-Mar 16-Mar 17-Mar 18-Mar
Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed
WO P P P WO P P P P P P P P P WO P P P

Your help would be much appreciated.

Thank you!

Thanks a lot. It worked out perfectly. :-)

Hi,

Will it be also possible to find out the total number of times these consecutive occurances has occurred in that range if it greater than 6?

In the below example this should return the value of 3. Since, the employee was present consequetively for more than 6 days three times.

WO P P P WO P P P P P P P WO P P P P P P P P WO P P P P P P P

Sorry for bothering you too much.

Thank you!