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...

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	client	region	value			Top N	3​
2​	A	North	100​			# clients	5​
3​	B	East	50​			Nth total	80​
4​	C	South	50​			Top N Adjusted	4​
5​	A	North	20​			client	score
6​	B	North	30​			A	120​
7​	C	East	60​			C	110​
8​	D	West	80​			B	80​
9​	E	Mid West	30​			D	80​
10​

<tbody>
</tbody>

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)),"")