multiple criteria within a date range

I am trying to utilise a formula with multiple criteria and a date range.

On the attached table I have 3 tabs.

Tab 1: “Priority”
This is where I’m creating two charts, tracking open and closed items in any given month (with a cumulative line).

Below the charts is the input table. I need to create the formula for this table.

Tab 2: “production”
This is where the raw data is dumped from the incident database. This cannot have anything modified on it, it is a pure data dump area.

Tab 3: “rawdata”
This is the tab I’ve used for data manipulation of information in the production tab. The tabs that pull this information I have deleted.

My dilemma is twofold.

Dilemma 1:

Working out how many issues were opened in a given month. All I need to do is select those that are defects within a time period.

Criteria I need to use:

1. Criteria of (production!D:D="Defect")
2. Dependent on which column in the output table they are in, they will be one of the following:
   • (production!E:E="Urgent");
   • (production!E:E="High");
   • (production!E:E="Normal"); or
   • (production!E:E="Low").
3. The dates are pulled from production!K:K, and will be dependent on the month

I've worked out I can use the reformatted date column rawdata!C:C to get this date.

Dilemma 2:

Working out how many issues were closed in a given month. This is where it gets trickier as I have to use:

1. All closed items, this is pulled from production!B:B and uses "Closed", "Rejected" and "Resolved"
2. They must have the criteria of (production!D:D="Defect")
3. Dependent on which column in the output table they are in, they will be one of the following:
   • (production!E:E="Urgent");
   • (production!E:E="High");
   • (production!E:E="Normal"); or
   • (production!E:E="Low").
4. The dates are pulled from production!Q:Q

I’m unsure what will work best for adding criteria 4 into the formula, I’ve utilised SUMPRODUCT previously to meet criteria 1-3 (above), but totally stumped as how I add in the date range.

If I need to I can use the “rawdata” tab to manipulate/format info from “production”


Modified & cut down version of the table attached.

Thank you in advance for your help.