Rank and Sumifs

I have a table of data with six columns: Create Date, Work Center, Material, Defect Type, Defect Location and Defect Qty.

Creation Date Work Center Material Defect Type Defect Location Defect Qty.
7/15/2013 QA-LINE 7210743-16 High / Low Spot A15 100
7/15/2013 QD-LINE 7210743-16 High / Low Spot P10 100
7/12/2013 QA-LINE 7210743-16 Buckle / Waves Y16 5
7/15/2013 QD-LINE 7210743-16 Burrs A15 32
7/15/2013 QD-LINE 7210743-16 Burrs A1 22

Is there a formula that will sum the Defect Qty. for each Defect Type (each Defect Type may appear multiple times with different quantities) using selection criteria and return the Defect Type with the highest quantity, second highest quantity, third highest quantity ... etc. I've looked at Rank, Large, Index and Match combinations, but I am unable to find a solution.

If possible, I would like to add selection criteria (e.g. Create Date is between Date 1 and Date 2, Work Center = QD-Line and Material = 7210743-16).

Please let me know if I posted incorrectly; this is my first time using the forum. Thank you!

Could provide workbook with data and desired result?

I had to walk through a procedure something like this recently and I ended up using a pivot table do my aggregations (so you wont have to deal with new data with the sumifs) then using a helper table to rank the data and THEN populating another table to show the summarized results in order of rank.

For me it was a list of biggest winners and losers in a particualr data set with 10k rows. There might be a more direct method, but I couldnt think of one that was easily auditable and decided to keep it simple and just use a helper tab.

Hi and welcome to the forum

To arrive at the answers you have in your workbook, use this, copied down...
=SUMIF($F$2:$F$58,$J2,$H$2:$H$58)

However, I notice that those totals dont include the data you have below row 58 (81:111)?

to include thise as well, change that formula to...
=SUMIF($F:$F,$J2,$H:$H)

If you want to bring in additional criteria/restrictions such as "Work Center = QD-Line and Material = 7210743-16" between certain dates, you need to change the formula to a sumifS(). So it then becomes...

=SUMIFS($H$2:$H$58,$F$2:$F$58,$J2,$D$2:$D$58,"QD-Line",$E$2:$E$58,"7210743-16") = 828

To rank these values, we could create another similar table and use the rank() or large() functions to order them

Thank you both for your responses.

I found the following information online, and I was able to modify it for what I need. Thanks again!

http://www.mrexcel.com/forum/excel-q...ring-text.html