Count the number of Instances

[louiscourtney]

I have a spread sheet that records sickness levels, what I'm after is a
formula or maybe even a macro that will return the about of sick instances
plus amount of days. The problem is that it needs to somehow have a bit that
due to shift patterns of 4 on 4 off reconises that if the gap is more than 5
days part from the last instance then it is a new instance if it is less than
5 then it's the same instance

Example

At the start of D3 running right out are the days of the month starting from
Jan 01 to Dec 31st
In A4 down to A100 are a list of names
If i use January as an example i need the answer to come to 3 instances and
a total of 12 days off

Cell
D4 = s
D5 = s
D6 = s
D7 = blank
D8 = Blank
D9 = blank
D10 = Blank
D11 = Blank
D12 = Blank
D13 = Blank
D14 = Blank
D15 = s
D16 = Blank
D17 = Blank
D18 = Blank
D19 = Blank
D20 = s
D21 = s
D22 = s
D23 = s
D24 = blank
D25 = blank
D26 = blank
D27 = blank
D28 = s
D29 = s
D30 = s
D31 = s

Hope this makes sense

 

[Bob I]

I think maybe you oversimplify, for instance if someone is sick the
first day and the last day of the shift cycle, then there are 2
instances, but the "2 day gap" would call it one instance.

 

[Toppers]

Slightly confused as I thought dates went ACROSS a row (per person), not down
a column as your example indicates (to me).

 

[louiscourtney]

Sorry Toppers you are right it should have been D4 E4 F4 G4 H4 I4 etc etc
Would be great if you can help

 

[Toppers]

Too late here in UK to start on this (!) as I think it will need a macro to
count the instances. I'll try and look tomorrow evening.