Count Consecutive Repeated Values including weekends

[johnbird1988]

Hello,

I would like some help with a Employee absence tracker I have created.

I would like to started recording the number of occurring sick absence in a year. My file looks like below.

The "S"represents a sickness, and for John the number of occurring sick absences are 2 because the 10 and 11 day is a weekend. For Dave the occurring sick absence is 3.

Any help would be amazing.

Thank you,

John

[TABLE="class: grid, width: 10, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]05/01/2015[/TD]
[TD]06/01/20150[/TD]
[TD]07/01/2015[/TD]
[TD]08/01/2015[/TD]
[TD]09/01/2015[/TD]
[TD]10/01/2015[/TD]
[TD]11/01/2015[/TD]
[TD]12/01/2015[/TD]
[TD]13/01/2015[/TD]
[TD]14/01/2015[/TD]
[TD]15/01/2015[/TD]
[TD]16/01/2015[/TD]
[TD]17/01/2015[/TD]
[TD]18/01/2015[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hello Aladin,

Just one more thing related to this post.

If I now only want to just count the number of times "S" appeared in a weekday (again excluding weekends and holidays), how can I achieve this, unlike the above where I want to count occurring "S" now I just want to count the number of "S".

I have tried this formula but it not work correctly.

[COLOR=#333333]=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S")*($J6:$NK6<>Holiday))[/COLOR]

Any help would be great.

Thank you,

John

I'm not sure how this is related the previous effort for we included weekends and holidays, not excluded. Is it possible that you provide a small sample alonge with the expected result?

 

[johnbird1988]

Thank you for replying. The my original example both John and Dave would be 4 because all there "S" area on a work day. The above formula will work butty is the holidays that are causing problem. I would like something similar to a count of that will count the number of "S" in a range but not if someone has marked a "S" against a date that is on a weekend or a date contained in the holiday table.

Hope this makes sense.

thank you for your help again,

John

 

[barry houdini]

I posted an answer in your earlier thread here