Rank / Index / Match

[Thoron6]

Morning all,

I have a long list of the example data below and would like to bring back the top 5 site names (based on highest number in "kWh" descending) where the manager is "Graham" and open / Closed is "Open".

Any help would be appreciated:

[TABLE="width: 372"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Manager[/TD]
[TD]Open / Closed[/TD]
[TD]kWh[/TD]
[/TR]
[TR]
[TD]Site 1[/TD]
[TD]Lee[/TD]
[TD]Open[/TD]
[TD]2,746[/TD]
[/TR]
[TR]
[TD]Site 2[/TD]
[TD]Graham[/TD]
[TD]Open[/TD]
[TD]2,458[/TD]
[/TR]
[TR]
[TD]Site 3[/TD]
[TD]Graham[/TD]
[TD]Closed[/TD]
[TD]1,643[/TD]
[/TR]
[TR]
[TD]Site 4[/TD]
[TD]Graham[/TD]
[TD]Closed[/TD]
[TD]4,312[/TD]
[/TR]
[TR]
[TD]Site 5[/TD]
[TD]Graham[/TD]
[TD]Open[/TD]
[TD]3,408[/TD]
[/TR]
[TR]
[TD]Site 6[/TD]
[TD]Graham[/TD]
[TD]Open[/TD]
[TD]580[/TD]
[/TR]
[TR]
[TD]Site 7[/TD]
[TD]Lee[/TD]
[TD]Open[/TD]
[TD]1,601[/TD]
[/TR]
[TR]
[TD]Site 8[/TD]
[TD]Lee[/TD]
[TD]Open[/TD]
[TD]890[/TD]
[/TR]
[TR]
[TD]Site 9[/TD]
[TD]Lee[/TD]
[TD]Open[/TD]
[TD]3,613[/TD]
[/TR]
[TR]
[TD]Site 10[/TD]
[TD]Lee[/TD]
[TD]Closed[/TD]
[TD]4,664[/TD]
[/TR]
[TR]
[TD]Site 11[/TD]
[TD]Brian[/TD]
[TD]Open[/TD]
[TD]4,255[/TD]
[/TR]
[TR]
[TD]Site 12[/TD]
[TD]Paula[/TD]
[TD]Open[/TD]
[TD]525[/TD]
[/TR]
[TR]
[TD]Site 13[/TD]
[TD]Brian[/TD]
[TD]Open[/TD]
[TD]3,529[/TD]
[/TR]
[TR]
[TD]Site 14[/TD]
[TD]Adrian[/TD]
[TD]Open[/TD]
[TD]3,986[/TD]
[/TR]
[TR]
[TD]Site 15[/TD]
[TD]Adrian[/TD]
[TD]Open[/TD]
[TD]2,335[/TD]
[/TR]
[TR]
[TD]Site 16[/TD]
[TD]Graham[/TD]
[TD]Open[/TD]
[TD]914[/TD]
[/TR]
[TR]
[TD]Site 17[/TD]
[TD]Adrian[/TD]
[TD]Open[/TD]
[TD]1,541[/TD]
[/TR]
[TR]
[TD]Site 18[/TD]
[TD]Phil[/TD]
[TD]Open[/TD]
[TD]4,026[/TD]
[/TR]
[TR]
[TD]Site 19[/TD]
[TD]Phil[/TD]
[TD]Open[/TD]
[TD]3,660[/TD]
[/TR]
[TR]
[TD]Site 20[/TD]
[TD]Phil[/TD]
[TD]Open[/TD]
[TD]3,628[/TD]
[/TR]
[TR]
[TD]Site 21[/TD]
[TD]Phil[/TD]
[TD]Closed[/TD]
[TD]1,506[/TD]
[/TR]
[TR]
[TD]Site 22[/TD]
[TD]Phil[/TD]
[TD]Open[/TD]
[TD]1,211[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	G	H
1	Name	Manager	Open / Closed	kWh	graham
2	Site 1	Lee	Open	2,746	open
3	Site 2	Graham	Open	2,458	5
4	Site 3	Graham	Closed	1,643	4
5	Site 4	Graham	Closed	4,312	Top N kWh	Top N Sites
6	Site 5	Graham	Open	3,408	3408	Site 5
7	Site 6	Graham	Open	580	2458	Site 2
8	Site 7	Lee	Open	1,601	914	Site 16
9	Site 8	Lee	Open	890	580	Site 6
10	Site 9	Lee	Open	3,613
11	Site 10	Lee	Closed	4,664
12	Site 11	Brian	Open	4,255
13	Site 12	Paula	Open	525
14	Site 13	Brian	Open	3,529
15	Site 14	Adrian	Open	3,986
16	Site 15	Adrian	Open	2,335
17	Site 16	Graham	Open	914
18	Site 17	Adrian	Open	1,541
19	Site 18	Phil	Open	4,026
20	Site 19	Phil	Open	3,660
21	Site 20	Phil	Open	3,628
22	Site 21	Phil	Closed	1,506
Sheet1 (2)

G1: 5 (desired Top N)

In G2 control+shift+enter, not just enter:

=COUNTIFS(B2:B23,G1,C2:C23,G2,D2:D23,">="&LARGE(IF(B2:B23=G1,IF(C2:C23=G2,D2:D23)),MIN(G3,COUNTIFS(B2:B23,G1,C2:C23,G2))))

In G6 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$6:G6)>$K$1,"",LARGE(IF($B$2:$B$23=$G$1,IF($C$2:$C$23=$G$2,$D$2:$D$23)),ROWS($G$6:G6)))

In H6 control+shift+enter, not just enter, and copy down:

=IF($G6="","",INDEX($A$2:$A$23,SMALL(IF($B$2:$B$23=$G$1,IF($C$2:$C$23=$G$2,IF($D$2:$D$23=$G6,ROW($A$2:$A$23)-ROW($A$2)+1))),COUNTIFS($G$6:G6,G6))))