Top 10 Clients without using a Pivot Table

[jon999]

Hi

Is there a formula that can work out the top 10 clients without using a pivot table.

For example

Client A North 100
Client B East 50
Client C South 50
Client A North 20
Client B North 30
Client C East 60

So from the above the formula needs to work it all out (client name, location, amount) so it would look like this
#1 Client A North 120
#2 Client C East 60

I know it is easier to use a pivot table but want to know if a formula can work this out.

Thanks

 

[AhoyNC]

Here's one way.
Copy formulas in E2, F2 & G2 down 10 rows.

NOTE: formulas in E2 & F2 are array formulas and must be entered with CTRI-SHIFT-ENTER (command-return on MAC)

Excel Workbook
	A	B	C	D	E	F	G
1	Client	Location	Amount		Client	Location	Amount
2	Client A	North	100		Client A	North	100
3	Client B	East	50		Client C	East	60
4	Client C	South	50		Client B	East	50
5	Client A	North	20		Client C	South	50
6	Client B	North	30		Client B	North	30
7	Client C	East	60		Client A	North	20
Sheet

 

[jon999]

Thanks AhoyNC for the reply but I need it to sum the amounts and then return the each Client once and each Location once.

For example

Client A North 100
Client A North 20

Will then return

Client A North 120

Thanks

 

[Michael M]

maybe

Excel 2007
	A	B	C	D	E	F	G
1	Client	Location	Amount		Client	Location	Amount
2	Client A	North	100		Client A	North	120
3	Client B	East	50		Client C	East	60
4	Client C	South	50		Client B	East	50
5	Client A	North	20		Client C	South	50
6	Client B	North	30		Client B	North	30
7	Client C	East	60
Sheet1

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--($A$2:$A$7=E2)*($B$2:$B$7=F2),($C$2:$C$7))
G3	=SUMPRODUCT(--($A$2:$A$7=E3)*($B$2:$B$7=F3),($C$2:$C$7))
G4	=SUMPRODUCT(--($A$2:$A$7=E4)*($B$2:$B$7=F4),($C$2:$C$7))
G5	=SUMPRODUCT(--($A$2:$A$7=E5)*($B$2:$B$7=F5),($C$2:$C$7))
G6	=SUMPRODUCT(--($A$2:$A$7=E6)*($B$2:$B$7=F6),($C$2:$C$7))

 

[Aladin Akyurek]

Thanks AhoyNC for the reply but I need it to sum the amounts and then return the each Client once and each Location once.

For example

Client A North 100
Client A North 20

Will then return

Client A North 120

Thanks

Could client A be a duplicate, leaving in different regions, that is, johnson living in north and Johnson living in south?

 

[Aladin Akyurek]

Could client A be a duplicate, leaving in different regions, that is, johnson living in north and Johnson living in south?

Additional clarification:

Client A North 100
Client A North 60
Client A East 80

Is this possible? That is, are your clients unique with respect to the location?

 

[jon999]

The information is coming from a database where the client is listed many times which has many sales. I am trying to work out through a formula to go down the list add up all the sales by client and return the top 10 just like it would in a pivot table by selecting all the data and then it list the client once with the total sales and then you can sort by top 10 etc.

 

[Aladin Akyurek]

The information is coming from a database where the client is listed many times which has many sales. I am trying to work out through a formula to go down the list add up all the sales by client and return the top 10 just like it would in a pivot table by selecting all the data and then it list the client once with the total sales and then you can sort by top 10 etc.

Additional clarification:

Client A North 100
Client A North 60
Client A East 80

Is this possible? That is, are your clients unique with respect to the location?

I'll just assume that a client is unique to a region...

[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]client[/TD]
[TD]region[/TD]
[TD]value[/TD]
[TD][/TD]
[TD][/TD]
[TD]Top N[/TD]
[TD]

3​

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

2​

[/TD]
[TD]A[/TD]
[TD]North[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]# clients[/TD]
[TD]

5​

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

3​

[/TD]
[TD]B[/TD]
[TD]East[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nth total[/TD]
[TD]

80​

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

4​

[/TD]
[TD]C[/TD]
[TD]South[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Top N Adjusted[/TD]
[TD]

4​

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

5​

[/TD]
[TD]A[/TD]
[TD]North[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]client[/TD]
[TD]score[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]B[/TD]
[TD]North[/TD]
[TD]

30​

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

120​

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

7​

[/TD]
[TD]C[/TD]
[TD]East[/TD]
[TD]

60​

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

110​

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

8​

[/TD]
[TD]D[/TD]
[TD]West[/TD]
[TD]

80​

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

80​

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

9​

[/TD]
[TD]E[/TD]
[TD]Mid West[/TD]
[TD]

30​

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

80​

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

10​

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

G1: 3 (Modify to 10 for your data.)

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

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),
    ROW($A$2:$A$9)-ROW($A$2)+1),1))

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

=LARGE(SUMIFS($C$2:$C$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),
    MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9)),
    MIN(G1,G2))

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

=SUM(IF(SUMIFS($C$2:$C$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),
    MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),
    $A$2:$A$9))>=G3,1))

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

=IF($G6="","",INDEX($A$2:$A$9,SMALL(IF(SUMIFS($C$2:$C$9,$A$2:$A$9,
    IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH($A$2:$A$9,$A$2:$A$9,0)),
    ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9))=$G6,
    ROW($A$2:$A$9)-ROW($A$2)+1),COUNTIFS($G$6:G6,G6))))

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

=IF(ROWS($G$6:G6)<=$G$4,LARGE(SUMIFS($C$2:$C$9,$A$2:$A$9,
    IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),
    ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9)),ROWS($G$6:G6)),"")