Help with grouping and then applying a conditional format

[darrenj1471]

Hi

Ive posted this in pivot section as I 'think' thats approach I need but happy to be told otherwise.
I have some raw data which Ive attached an example of and effectively its some rows with a start and end date and a type. I want , on a regular basis to extract data like this (wll be dynamic numbers of rows each qtr) and develop a KPI type summary.

What Id like to do is end up with 3 x 3 row summaries which effectively query the Type value in column B and then count how many rows with that type are between X, Y and Z days in column E

So Id like only those rows to be considered which have Type 1 (for example)
1. Count of rows where value in E is between 0-14 days
2. Count of rows where value in E is between 15-28 days
3.Count of rows where value in E is greater than 28 days

Type 2,3 and 4
1. Count of rows where value in E is between 0-90 days
2. Count of rows where value in E is between 91-180 days
3.Count of rows where value in E is greater than 180 days

I thought about 3 pivots with the Types as filters but then I dont know how to group the rows?

Much appreciated in advance for help

[modytrane]

Take a look at the attached file.
Is this something you can use?

When you click on "Update" button, it creates a unique list of items from column B.
Then the table uses formulas to give you count of rows matching the item and days range.

Formula for counting is typically:

for first one [i.e. <=14]

for others [i.e. between 14 and 28]

[darrenj1471]

This is looking really good thanks. I have a couple of questions/points though.

1. The location Im running this at blocks macros from what I can tell so the handy update button isnt working. What is the impact of not having this? ie could the table be refreshed another way?
2. Columns E and F appear to be identical and I see no formula referencing column F so can this be deleted?
3. I dont actually need to count the outcomes by Type but instead need to bucket the types into 3 buckets and then count how many of each bucket fall into a range of days. I assume Id need to maintain a table in another sheet mapping type to bucket ? I dont really know how to then make the countif consider those buckets?

[darrenj1471]

Hi

Ive attached the workbook you did and Ive added the KPI sheet which is my goal. Id like to end up with a number in the Red, Amber and Green boxes. These boxes are aligned to the buckets for the Groups. This is the final piece of puzzle hence why I was asking about using Countif on top of the work youve done

[JeteMc]

I attempted to match the 'bucket' tables on the 'Lists' sheet to the Groups on the KPI dashboard sheet, so I hope that I matched them up correctly.
The formula for the 'Bucket' column on the 'Raw Data' sheet is modified by to read: =IFERROR(VLOOKUP([@[Days in Negotiation]],INDIRECT("Table_Bucket"&[@Group]),2,TRUE),"")
The formulas on the KPI dashboard sheet are similar to: =SUMPRODUCT((Table1[Group]=1)*(Table1[Bucket]="0-90"))/COUNTIF(Table1[Group],1)
Let us know if you have any questions.