Count of Consecutive Days OFF Formula

[Enan Bahadur]

Dear Excel Guru's
Kindly need your help to get the consecutive 02 days OFF and 04 days OFF count for my staff roster separately.
I want to get the values as shown in green cell.
Attached excel file is for your reference and above image as well.

 

[ETAF]

how about
for 2
=SUM(IF(FREQUENCY(IF(D4:p4="OFF",COLUMN(D4:p4)),IF(D4:p4<>"OFF",COLUMN(D4:p4)))=2,1))
for 4
=SUM(IF(FREQUENCY(IF(D4:p4="OFF",COLUMN(D4:p4)),IF(D4:p4<>"OFF",COLUMN(D4:p4)))=4,1))

Not sure what version of excel you have - but may need
CONTROL+SHIFT+ENTER..


Just change the number 2 to 4 to mark the amount you want
=SUM(IF(FREQUENCY(IF(D4:p4="OFF",COLUMN(D4:p4)),IF(D4:p4<>"OFF",COLUMN(D4:p4)))=2,1))

NOT sure it will work for all possible

the face is : and a P
so D4 : P4
Moderator note: You could use Inline code to avoid that.

 

[Enan Bahadur]

how about
for 2
=SUM(IF(FREQUENCY(IF(D44="OFF",COLUMN(D44)),IF(D44<>"OFF",COLUMN(D44)))=2,1))
for 4
=SUM(IF(FREQUENCY(IF(D44="OFF",COLUMN(D44)),IF(D44<>"OFF",COLUMN(D44)))=4,1))

Not sure what version of excel you have - but may need
CONTROL+SHIFT+ENTER..


Just change the number in red to the amount you want
=SUM(IF(FREQUENCY(IF(D44="OFF",COLUMN(D44)),IF(D44<>"OFF",COLUMN(D44)))=2,1))

NOT sure it will work for all possible

the face is : and a P
so D4 : P4

Thanks a lot ETAF, It is working perfectly.

 

[ETAF]

you are welcome
it may fall over for some conditions

 

[Enan Bahadur]

you are welcome
it may fall over for some conditions

Sorry i did not understood
As of now the only requirement is days OFF count.

 

[ETAF]

ok, thats how it works, just away anything more - may cause issues i did not fully test ver lots of data - just the sample you supplied