Weekly report with dynamic data

I am creating an automated weekly summary report for our organization's noncompliances. The summary report refers to another worksheet (log) which is updated on a daily basis or as needed.

Our log contains the following info:
- department or project
- status (open, closed, rejected, escalated, etc.)
- date detected
- nc due date
- date closed
- level of severity (minor, major, observation)
- reporting period

Every week, we keep track of the open items per level of severity. Items that are closed e.g. during the week of Sept 18 to 22 should not be included in the weekly summary report for Sept 25.

I've used the SUMPRODUCT funtion (using the department or project, status, and level of severity as criteria). The values generated by the function are correct with respect to the current date. So any changes made to the worksheet, the weekly summary report is also updated.

My problem is how do I keep the weekly data (e.g. Sept 18 to 22) from retaining itself. I feel that I have a missing criteria in my SUMPRODUCT function but I just can't seem to figure it out. I've used the 2 dates but the values are not correct.

Help please ...

if the status (open, closed, overdue, etc) updates via a formula that looks at due dates and current dates (for overdue) then running a report any day will give you the true picture on that day. Run it tomorrow, especially if tomorrow is the first of the month, you may get more overdues, as most actions are set for close out by the end of a month. Sumproduct should work fine, therefore.

sorry, I missed a vital point. Put in an extra column in your table which flags if the corrective action was closed "this Week". Then insert an extra bit into your sumproduct formula which ignores rows where the flag is "on".