Frequency formula help

[TS2021]

Hope everyone is well.

I learn so much from these forums. I truly cant thank you folks enough!

Hope you can help me.

Goal: To find total number of consecutive days off each employee has taken. So if an employee has taken days off on Thurs n Fri then count as 1. If Thurs- Fri- Sat then count should be 2. So in the example provided below count of consecutive days off should be 18.
Data: F3:AJ3 (3 through 2000)
Formula applied: =MAX(FREQUENCY(IF(F3:AJ3>1,ROW(F3:AJ3)),IF(F3:AJ3<1=0,ROW(F3:AJ3)))) in AM3 for total days off taken by employee XYZ and =COUNTIF(F3:AK3,"OFF")-SUMPRODUCT(--(F3:AJ3="OFF"),--(F3:AJ3<>G3:AK3),--(G3:AK3=H3:AL3)) in AN3 for total instances of consecutive days off taken.
AM3 should be providing consecutive instances for days taken off but when its copied 3 through 2000 some rows have incorrect consecutive instances. Goal is to ensure that AN reflects total number of consecutive days off taken by each employee 3 through 2000.

I would truly appreciate your help. Thanks a ton Excel Experts

 

[jasonb75]

Based on that, I believe that @bebo021999's suggestion in post 5 will be the correct solution. If it is not giving you the results that you need then we would need to see some examples of where it is giving the wrong result, along with the result that you expect in order to understand why.

 

[TS2021]

I created a new file and re-tried the formulas and its working. Yayyyy!!! I cant thank you enough. Kudos @bebo021999 @jasonb75

 

[bebo021999]

Nice to see it works. It could be better if using VBA code.