How to highlight consecutive dates meeting multiple criteria

[divi123]

Hi Seniors, please help me out in highlighting consecutive dates meeting multiple criteria in the given Attendance data.

Sample sheet attached. Criteria : Emp no & if Leave type is 'CL or SL'

Thanks in advance !

Regards,

Divya

[:) Sixthsense :)]

In H2 Cell

=IF(OR(F2={"CL","SL"}),IF(COUNTIFS(B:B,B2,D:D,D2-1)+COUNTIFS(B:B,B2,D:D,D2+1),"Yes","-"),"-")

Drag it down...

[divi123]

I'm getting an error #NAME?

please explain the formula

i'm using 2003 version, think its the error with countifs

[:) Sixthsense :)]

Oopss.. try this...

In H2 Cell

=IF(OR(F2={"CL","SL"}),IF(SUMPRODUCT(($B$1:$B$19=B2)*($D$1:$D$19=(D2-1))+($D$1:$D$19=(D2+1))),"Yes","-"),"-")

Drag it down...

[divi123]

Perfect.. thanks..

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep