SUMPRODUCT formula issues

Please see attached.

I am having some issues around the SUMPRODUCT formula


1) Top 10 Sheet - Looking to populate Column C with the sum of the QTY(Source: Error Report Data Column K) column for each of the Defects listed in Column A.

2) 1st, 2nd and 3rd Top defect sheets, Having issues calculating the TOP 10 Components (F7:F16) from the Component list (Column A). The Top 10 values in Column G are correct, just the name is not corresponding correctly.

3) As well on the 1st, 2nd and 3rd Sheets, similar to the first issue, calculating the QTY (Source: Error Report Data Column K) but instead by defect it will be by Component listed in Column A

Hope this makes sense and I greatly appreciate anyone's time in advance for helping me out!

sample.xlsx

Here is the formula for your QTY calculation using SUMIFS. Put this in C4 and then drag down.
=SUMIFS('Error Report Data'!$K$2:$K$1613,'Error Report Data'!$J$2:$J$1613,A4,'Error Report Data'!$A$2:$A$1613,"<="&$H$5,'Error Report Data'!$A$2:$A$1613,">="&$H$4)

You may want to convert some of your sumproducts (e.g. in column B) to SUMIFS or COUNTIFS since they are faster than the array centric SUMPRODUCT.

As for your second issue, that is a common problem people have with excel. You are essentially looking for a VLOOKUP that can return matches beyond the first match. I think you are very close, but here is a website that gives a bit more detail.
http://www.globaliconnect.com/excel/...=77&Itemid=473

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:

Please Login or Register  to view this content.

Copy down
B4

Formula:

Please Login or Register  to view this content.

Copy down
G4

Formula:

Please Login or Register  to view this content.

Copy down.
F4

Formula:

Please Login or Register  to view this content.

Copy down
E4

Formula:

Please Login or Register  to view this content.

Copy down.

No array formulas on that sheet.

1st & 2nd & 3rd Top Defect sheets
A4

Formula:

Please Login or Register  to view this content.

Copy down
B4

Formula:

Please Login or Register  to view this content.

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