Count of adjacent values

[dancing-shadow]

I am trying to create an absence planner for work, which totals up the amount of instances and days an individual is away from work based on a calendar-type table.

I'm struggling to create the correct formula which will count the amount of instances, as in one month an individual could be off on 3 seperate occasions, but this could split out to 1 day, 5 days and 2 days, so 8 days in total). I need excel to calculate this as such. I've attached a file which I hope will demonstrate what I am trying to achieve.

sickness file.xlsx

Thanks in advance

[canapone]

Hi,

in AI2 and down

=SUMPRODUCT(--(((WEEKDAY($B$1:$AE$1,2)<6)*(B2:AE2="S")+(WEEKDAY($B$1:$AE$1,2)>5)*((A2:AD2="S")+(C2:AF2="S")))&((WEEKDAY($C$1:$AF$1,2)<6)*(C2:AF2="S")+(WEEKDAY($C$1:$AF$1,2)>5)*((B2:AE2="S")+(D2:AG2="S")))="01"))


I hope you'll receive better solutions.

Cheers

[dancing-shadow]

Wow that's great thank you

I have managed to adapt it to my data, and it does work, however I totally forgot to ask for one more criteria I need...

The dates in row 1 already run from 2012 - I need to add in a criteria to only count up these instances where the date is between today() and today()-365...

[canapone]

Hi, thanks for your generous feedback but the naive formula is not meeting all expectations: I hope it could be of some ispiration.

I don't give up.

Cheers

[canapone]

Hi,

formula looks working: please make some tests

Please Login or Register  to view this content.

Regards

[dancing-shadow]

That is perfect! So many thanks for your time and effort, it's much appreciated!

[canapone]

Hi,

thanks for kind feedback: please test the formulas as much it's possible to insure they're accurate.

Greetings from Firenze