Getting Top 3 based on Date Range

[antexity]

Please see attached.

Tab - Error Report Data contains all detail

Tab - Top 3 for each Month Last 12 contains the summary data, top 3 and chart

What I am trying to achieve is the Top 3 for each of the month ranges for the last 12 months. A37:A38 (Tab "Top 3") is the formulated Month Range, Column B are the Months/YY for Charting and Column C are the Top 3 Defects from (Tab "Error Report.." Column I) and Column D are the top 3 % from (Tab "Error report.." Column N)

The Top 3 columns, if the Dates from Column A (Tab : Error Report) are in between the range of Dates Column A (Tab: "Top 3") Then give me the Top 3 Defects and Its %

The Difficult thing is that the values on (Tab: Error Report) are duplicated and Columns L:N are Summarized from Column I:J

Actual Expected Results are on (Tab: Top 3). There are months where there are no data but needs to be calculated as eventually there may be data. Result would be 0 or N/A etc.

Thanks so much.

testv11.xlsx

[soberguy]

This was fun. Note that all three are array formulas and should be entered with Control+Shift+Enter.

For D36:
=LARGE(IF($A$37<='Error Report Data'!$A$2:$A$390,IF($A$38>='Error Report Data'!$A$2:$A$390,'Error Report Data'!$N$2:$N$390)),1)

D37, then copy D37 and paste into D38:
=LARGE(IF($A$37<='Error Report Data'!$A$2:$A$390,IF($A$38>='Error Report Data'!$A$2:$A$390,IF('Error Report Data'!$N$2:$N$390<D36,'Error Report Data'!$N$2:$N$390))),1)

[antexity]

"This was fun" Made me laugh, Thanks so much, It works great. Do you know of a way to auto populate the C columns the Defect Codes that goes with the %?

[soberguy]

in C36, also an array formula (Ctrl+Shift+Enter):
=INDEX('Error Report Data'!$A$2:$N$390,MATCH(D36&1&1,'Error Report Data'!$N$2:$N$390&IF($A$37<='Error Report Data'!$A$2:$A$390,1)&IF($A$38>='Error Report Data'!$A$2:$A$390,1),0),9)

Verify that it returns the right values. Not sure what it should return, really. If you look at 3rd place, with 66.7%, there are multiple Def Codes in I (the column the formula returns), and it simply snatches the first one - which isn't the result you previously had in there.

[antexity]

Outstanding response time! thank you so much for your time. Much Appreciated

[soberguy]

Glad to be of service.