Array formula with top x results

[Charles Bushby]

Hi,

I need to use an array formula in Excel to summarise data from a table. The csv table is at the bottom of this posting. I want to summarise the top 5 clients (i.e. excludes Administration in Type field) where the hours are greater than zero and Invoiced is zero. My summary should look like this:

Top 5 uninvoiced
Work, Hours
Client L, 12
Client K, 8

Using an array formula I can successfully extract the hours but my array for the Work pulls out the wrong clients (namely Clients F and E). This is because the formula dumps the first clients with 12 and 8 hours. I therefore need a more complex formula that select the correct clients per the top para. above.

Any help with this would be much appreciated.

Charles Bushby

Work,Type,Hours,Invoiced
Client A,Client,60,7500
Client B,Client,5,10
Client C,Client,2,500
Client E,Client,8,900
Administration,Administration,31,
Client F,Client,12,500
Client G,Client,10,800
Client H,Client,12,650
Client I,Client,10,600
Client J,Client,10,750
Client K,Client,8,
Client L,Client,12,
Client D,Client,12,1000

 

[Aladin Akyurek]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Top[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]invoiced[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]

client​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Adjusted[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Work[/TD]
[TD]Type[/TD]
[TD]Hours[/TD]
[TD]Invoiced[/TD]
[TD][/TD]
[TD]Work[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Client A[/TD]
[TD]Client[/TD]
[TD]

60​

[/TD]
[TD]

7500​

[/TD]
[TD][/TD]
[TD]Client L[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Client B[/TD]
[TD]Client[/TD]
[TD]

5​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]Client K[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Client C[/TD]
[TD]Client[/TD]
[TD]

2​

[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Client E[/TD]
[TD]Client[/TD]
[TD]

8​

[/TD]
[TD]

900​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Administration[/TD]
[TD]Administration[/TD]
[TD]

31​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Client F[/TD]
[TD]Client[/TD]
[TD]

12​

[/TD]
[TD]

500​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Client G[/TD]
[TD]Client[/TD]
[TD]

10​

[/TD]
[TD]

800​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Client H[/TD]
[TD]Client[/TD]
[TD]

12​

[/TD]
[TD]

650​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Client I[/TD]
[TD]Client[/TD]
[TD]

10​

[/TD]
[TD]

600​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Client J[/TD]
[TD]Client[/TD]
[TD]

10​

[/TD]
[TD]

750​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]Client K[/TD]
[TD]Client[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Client L[/TD]
[TD]Client[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]Client D[/TD]
[TD]Client[/TD]
[TD]

12​

[/TD]
[TD]

1000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

=SUM(COUNTIFS(B6:B18,G3,D6:D18,{"",0},C6:C18,">="&LARGE(IF($B$6:$B$18=G3,
  IF(D6:D18=G2,C6:C18)),MIN(G1,SUM(IF(B6:B18=G3,IF(D6:D18=G2,1)))))))<strike></strike>

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

=IF($G6="","",INDEX($A$6:$A$18,SMALL(IF($B$6:$B$18=$G$3,
  IF($D$6:$D$18=$G$2,IF($C$6:C$18=G6,ROW($C$6:C$18)-ROW($C$6)+1))),
  COUNTIFS($G$6:G6,G6))))<strike></strike>

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

=IF(ROWS($G$6:G6)<=$G$4,LARGE(IF($B$6:$B$18=$G$3,
  IF($D$6:$D$18=$G$2,$C$6:$C$18)),ROWS($G$6:G6)),"")<strike></strike>

 

[Charles Bushby]

Thanks Aladin. That worked perfectly.

Charles

 

[Aladin Akyurek]

Thanks Aladin. That worked perfectly.

Charles

You are welcome. Thanks for the update.