Formula to sort large data pool

I am assuming you mean companies for the top 5

Use this formula, changing the RED to whatever rank number you need:


=INDEX(B:B,MATCH(LARGE(E:E,1),E:E,0))

Then make a formula like this, which in which the RED color is the cell you put the first formula in

=SUMIFS(E:E,B:B,I11,C:C,VLOOKUP(G10,B:C,2,0))

Actually, that doesn't do it right (and i am leaving now so i can't revise! sorry!) but maybe it'll give ideas, or at least give you a free bump!


Good luck.

This can be solved with a pivot table.

First I set up the pivot table with the items as shown in the Layout box on Sheet1. Then I went to each of the row headers and right clicked on it and selected Field Settings. In the resulting dialog box I selected None for the total and on the next tab in the dialog box: show items in tablular form.

This produces a tablular report. Since you have Excel 2010 or later you can also select Design -> Report Layout -> Repeat all items labels under the Pivot table ribbon.

To get the top 6, select a cell in the territory part of the table, and then go to the filter in the Row Labels cell. Select Values filter -> Top 10 and select top 6 by item for Sum of FY16

To add the shading I recommend using conditional formatting to compare column C to column D. I've had problems in the past with conditional formatting and pivot tables. As the tables refresh, sometimes the range to which the conditional formatting is applied gets scrambled.

I copied over your desired output to do a quick QA.