Hi
Sumproduct array will slow your file down? That reason why Top 10 table not working??
So, I clear all your formula on all sheets.
Top 10 Defects last 12 months sheet
A4
Formula:
=IFERROR(INDEX('Error Report Data'!J$2:J$1700,MATCH(1,INDEX((COUNTIF(A$3:A3,'Error Report Data'!J$2:J$1700)=0)/('Error Report Data'!A$2:A$1700>=$H$4)/('Error Report Data'!A$2:A$1700<=$H$5),0),0)),"")
Copy down
B4
Formula:
=COUNTIFS('Error Report Data'!$A$2:$A$1613,">="&$H$4,'Error Report Data'!$A$2:$A$1613,"<="&$H$5,'Error Report Data'!$J$2:$J$1613,A4)
Copy down
G4
Formula:
=AGGREGATE(14,6,$B$4:$B$200,ROWS($1:1))
Copy down.
F4
Formula:
=INDEX($A:$A,SMALL(INDEX(($B$2:$B$200<>$G4)*10^10+ROW($B$2:$B$200),0),COUNTIF($G4:$G$7,$G4)))
Copy down
E4
Formula:
=IFERROR(AGGREGATE(14,6,'Error Report Data'!$M$2:$M$1613/('Error Report Data'!$J$2:$J$1613=A4),ROW($1:1)),"")
Copy down.
No array formulas on that sheet.
1st & 2nd & 3rd Top Defect sheets
A4
Formula:
=IFERROR(INDEX('Error Report Data'!G$2:G$1700,MATCH(1,INDEX((COUNTIF(A$3:A3,'Error Report Data'!G$2:G$1700)=0)/('Error Report Data'!J$2:J$1700=$F$4)/('Error Report Data'!A$2:A$1700>=$H$4)/('Error Report Data'!A$2:A$1700<=$H$5),0),0)),"")
Copy down
B4
Formula:
=COUNTIFS('Error Report Data'!$A$2:$A$1613,">="&$H$4,'Error Report Data'!$A$2:$A$1613,"<="&$H$5,'Error Report Data'!$G$2:$G$1613,A4,'Error Report Data'!$J$2:$J$1613,$F$4)
Copy down
G7 & F7 &E4 is same formulas as Top 10 Defect sheet.
No Array on all 3 sheets.
You will notice now it a lot faster as Countifs run faster than array Sumproduct or no array Sumproduct and a little faster than sumifs.
See the files
Any problem pm me
Regard
Bookmarks