Rank Top 5 Customers in a list by the sum of revenue

loon027

New Member
Joined
Jul 9, 2014
Messages
2
I've gone through and done my legwork to try and find this online after trying many different methods that I know. This is the first one that I can't get an answer for by looking online. It seems easy enough to do. I'm trying to not use a pivot table and have a dashboard to rank the customers by revenue. I'd like to display top 5. Any help I appreciated. My example list is below:

Customer Revenue <----Header
A 2000
X 1500
C 2200
A 8200
D 4000
C 500

etc.... for hundreds of rows

I'm looking to have an array formula that would dynamically sum the customers revenue by name. Example
Rank Customer Revenue
1st A 10200
2nd D 4000
3rd C 2700
 

Kaper

Board Regular
Joined
Mar 14, 2014
Messages
228
The easy way will be to:
1) create list of unique customers (say in column M, starting M2) - either with built in tools - remove duplicates or advanced filter or formula (see for instance http://www.mrexcel.com/forum/excel-questions/439486-extracting-unique-values-list-using-formula.html)
2) in N2 formula (and copy down):
Code:
=SUMIF(A:A,M2,B:B)
3) in E2 formula (also copy down):
Code:
=LARGE($N$2:$N$10,ROW(A1))
4) in D2 (and down):
Code:
=INDEX($M$2:$M$10,MATCH(E2,$N$2:$N$10,0))
adjust last row ($10) in the formulas above to be greater than expected number of unique customers

The more advanced would be with packing it all into large array formula, but it would be probably not only more complicated, but also less effective and would slow down your spreadsheet.
 
Last edited:

loon027

New Member
Joined
Jul 9, 2014
Messages
2
The easy way will be to:
1) create list of unique customers (say in column M, starting M2) - either with built in tools - remove duplicates or advanced filter or formula (see for instance http://www.mrexcel.com/forum/excel-questions/439486-extracting-unique-values-list-using-formula.html)
2) in N2 formula (and copy down):
Code:
=SUMIF(A:A,M2,B:B)
3) in E2 formula (also copy down):
Code:
=LARGE($N$2:$N$10,ROW(A1))
4) in D2 (and down):
Code:
=INDEX($M$2:$M$10,MATCH(E2,$N$2:$N$10,0))
adjust last row ($10) in the formulas above to be greater than expected number of unique customers

The more advanced would be with packing it all into large array formula, but it would be probably not only more complicated, but also less effective and would slow down your spreadsheet.

Thanks Kaper!
I was hoping to just have an array formula in only 5 cells on my summary tab vs having helper columns and lots more formulas. The main reason for that is I'm no going to be the one maintaining it so it needs to be self sufficient.
 

Forum statistics

Threads
1,085,863
Messages
5,386,395
Members
401,997
Latest member
cizwiz

Some videos you may like

This Week's Hot Topics

Top