SumIfs with Rolling Calendar

[haleyarmstrong]

Employee ID #	First Name	Last Name	Team	Date of Occurrence	Type of Occurrence	Occurrence Points
111111	Haley	G	Test	1/1/14	Absent	1
111111	Haley	G	Test	4/1/14	Absent	1
111111	Haley	G	Test	7/1/14	Absent	1
111111	Haley	G	Test	10/1/14	Absent	1
111111	Haley	G	Test	1/1/15	Absent	1
111111	Haley	G	Test	4/1/15	Absent	1
111111	Haley	G	Test	7/1/15	Absent	1

<tbody>
</tbody>

I am working to create an attendance tracking system for work. At work, we assign points for every occurrence which you are absent/tardy/leave early & these points are totaled on a 6 month rolling calendar. Each employee has a unique ID number which is what I have been using as a criteria for the SumIf function.

However, now that we have the rolling calendar in place, I need to have points that expire after 6 months to roll off & not be included in the SumIf calculation while still evaluating employee ID number. I am not sure how to go about this. I would think that adding date ranges as criteria would work but I do not know how to do this. I have created a table of sample data. Any help would be greatly appreciated, thank you .

 

[Weazel]

how about just expiring the point in the table after 6 months ?

maybe something like =IF(TODAY()-E2>180,0,1) in the Occrrence Points column ?

obviousely though the points for 2015 wouldn't be valid