Calculating Total Sickness in a rolling 12 month Period

Hi

Wonder if someone can help with my issue please.
I have a spreadsheet that contains about 2 years worth of data, and i want to calculate the total sickness in a rolling 12 month period for each employee different employee within the sheet.
All the different employees and their dates off during those years are in the same spreadsheet. Is there any way i can get this please, for each individual employee as well as the trigger for each person if it applies please.

1. total days off for rolling 12 months
2. Trigger for different absences

I have attached an example sheet, tab1 is the triggers and tab 2 is the data.
Any help will be much appreciated. Thank you

Thanks

What are the "triggers"?

The triggers are

Trigger Rolling 12 month period
1 5 instances
2 6 Instances
3 7 Instances

So i need a trigger for 5 instances of sick in the last 12 months, not really sure if a trigger is a colour or something else.

Thank you for your response.

The triggers are

Trigger Rolling 12 month period
1 5 instances
2 6 Instances
3 7 Instances

So i need a trigger for 5 instances of sick in the last 12 months etc

Is it a trigger per Category?

See attached: basic formula is ...

=IF(SUMPRODUCT(--(Data!$B$2:$B$14=A3),--(Data!$A$2:$A$14=B1),--(Data!$C$2:$C$14>EOMONTH(TODAY(),-12)+1))=5,"Yes","")

Counts occurrences of absence category in last 12 months (currently from 1/10 2014 until TODAY()) for each employee.

Is this what you want?

Am probably not explaining this too well sorry but what i want the sheet to do is to return in a cell of a new column:
* "TRIGGER 1" if a particular person has been absent on 5 separate instances within the last 12 month period
* "TRIGGER 2" if a particular person has been absent on 6 separate instances within the last 12 month period
* "TRIGGER 3" if a particular person has been absent on 7 separate instances within the last 12 month period

so the cell returning should check the persons name, check for every time that name appears in the column, then check if the dates that the persons name appear are within the last 365 days and return "TRIGGER x" if one of the rules given above have been met.

Hope that makes sense.

Our posts have probably crossed. My reading of your last post is that count is for total of any absence category: i.e 3 Sick + 2 illness +1 absence = Trigger 1(?)

Yes. The absence categories do not need to be differentiated. For the sake of this task, any category can merely be regarded as an absence and should be looked at not as sick or illness but rather as simply an absence.

Try

Trigger 1

=IF(SUMPRODUCT(--(Data!$B$2:$B$91=$A3)*((Data!$A$2:$A$91="Sick")+(Data!$A$2:$A$91="Illness")+(Data!$A$2:$A$91="Absence"))*(Data!$C$2:$C$91>EOMONTH(TODAY(),-12)+1))=5,"yes","")

Hi

Hi
I don't want to include the absence category in any way, i can see that your formula is adding the different categories.

All i want the calculation to do is see how many occurrences for a particular name in the last 12 months and bring back a Trigger if the name appears the total amount of instances for that trigger.

e.g if "Emma" appears 5 times in the last 12 months then that's Trigger 1

Thank you for your help really appreciate it.

=IF(COUNTIFS(Data!$B$2:$B$14,$A3,Data!$C$2:$C$14,">"&EOMONTH(TODAY(),-12)+1)=5,"Trigger 1","")

Counts if name appears 5 times in 12 month period, irrespective of category.

Hi

This doesn't seem to be working

Post a file with sufficient data : it worked OK for me when I ensured data was within 12 months. All of your test data was not within 12 months of today ( some dates were July 2014).