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:

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

<tbody>
</tbody>

 

[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))))