multiple criteria within a date range

[sangomas]

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.

[hemesh]

Hello Find attached!!


I think because of less data formula is not giving the expected results(As shown by you below the calculation part). Try this formula with your actual data and get back if there is any problem,
I have formatted your sheet to keep only one formula, In your actual sheet format as same then drag across and down.

excelhelp (1).xlsx