Sumproduct, attendance review

[anaschiliro]

Good afternoon,

I ran a year-to date (YTD) attendance report on my team of 3000 employees. Those with more than 3 sick days within that period had verbal coaching. They are flagged thru conditional formatting whenever the number 3 comes up on the Sick column.

Every week I ran a WTD report to see if their attendance have improved, but also to check if employees that weren't flagged before (with 0, 1, or 2 absences), have fallen into the flagged category (3) where now they need verbal coaching. I'm trying to avoid speaking to people that I have already coached, and trying not to miss someone who's in need of a warning.

I have 3 sheets as described below:

Sheet 1:
Data Period: 03/01/2016 -02/25/2017

This is my YTD information. In this example, I'd have to coach Barbara bc of her 3 absences.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]
[TABLE="width: 135"]
<tbody>[TR]
[TD]EMP #
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]Name
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 21"]
<tbody>[TR]
[TD]SICK
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 44"]
<tbody>[TR]
[TD]HOURS
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]ALBA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]BARBARA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]CHARLIE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]DANIEL
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]ELLE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2:
Data Period: 2/26/2017 - 02/27/2017
This is my WTD information. I'll use this info to calculate the difference between YTD and current data and find out patterns of absence that need attention.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]
[TABLE="width: 135"]
<tbody>[TR]
[TD]EMP #
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]Name
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 21"]
<tbody>[TR]
[TD]SICK
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 44"]
<tbody>[TR]
[TD]HOURS
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]ALBA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]BARBARA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]CHARLIE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]DANIEL
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]ELLE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 3: RESULTS wanted on this sheet.
I'll need to coach Alba because the sum of her previous and current absences is equal 3.
Barbara hasn't accumulated any absence since our coaching session, so I don't take her YTD data into account.
Daniel and Elle have their YTD and WTD data combined, but would only be addressed if this equals 3 or more.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]
[TABLE="width: 135"]
<tbody>[TR]
[TD]EMP #
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]Name
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 21"]
<tbody>[TR]
[TD]SICK
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 44"]
<tbody>[TR]
[TD]HOURS
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]ALBA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]BARBARA
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]CHARLIE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]DANIEL
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[TABLE="width: 56"]
<tbody>[TR]
[TD]ELLE
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2
[/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]


Please help me calculate the difference between these tables.
YTD will remain the way it is. WTD will change every week and the results must adjust to that.
I'd like the Employee number to be used as a reference in all 3 sheets, since its unique amongst all 3000 employees.

Please let me know if more details are needed.

TYIA,

Ana