Top 10 report based on totals of sales from a list (Rank top ten)

Hi All,

I have a data in following order

Column A...........Column B..........Column C
x Com.................10$................1.1.2012
y Com.................37$................2.2.2012
x Com.................20$................5.2.2012
z Com.................43$................10.3.2013 and so on
Y Com................12$
x Com.................22$
y Com.................123$
z Com.................24$

Column A shows the company names there lots of them (over 300) and Column B shows the individual sales amounts for each transaction. Column C is the date of the transaction covering 3 years or more.

In a separate report I am trying to show top 10 companies by sales $s depending on a year selected by user (lets say 2012 is selected)

My problem is I cannot rank company names for the top 10. It needs to be a formula as this will be a dynamic report. Formula should get the total for each company for the selected year (which I can write this with SUMIFS function) and then return the name of the company with the highest sales for 2012 (selected year). then 2nd row 2nd highest and so on.

Heelllppp?

Thanks in advance.

Egemen

Are you saying the same company can be listed many times, once for each transaction. The easiest way to do this is with a pivot table. a sort and subtotal sheet with conditional formatting or the rank function would show the top 10. Lots of ways to do this it just depends on how much flexibility you have to change the sheets.

I thought about using pivot but i need to incorporate some other information into the report like number of items sold etc. So Pivot does not give me exactly what I needed but an option if I cant find a more suitable one.

As the list is quite big I cannot apply Rank combined with sumifs to each customer as it will really slow down the system.

I need a separate tab just showing me the top 10 company names according to their sales and from here I can apply to show the sales volumes, sales $s, and some other info.

File is attached,

Raw data is how I get the reports and I am trying to achieve the tab called report. Numbers in report tab are made up...

I added a column to your 'Raw Data' sheet called "Gross Sales" (I have no Idea if it works correctly to calculate your gross sales numbers). I then put the data into a pivot table with the customer name, sum of units and sum of gross sales. Then I filtered the "Row Label" column with the "value filter" to "top 10" based on the gross sales.

Check the attached file and see if it works for you.

Tom

Thanks Guys,

Very helpful indeed.

Was trying to avoid pivoting as I was also trying to show the results as chart and when you chart the pivot it messes up with axis and legend and actually you cannot manually adjust it without changing the layout of the pivot.

Having searched what I am looking for actually no straight forward way to deal with it as found few array formulas working but massively affecting the performance.

Cheers and have a good day...

I totally missed the part about the year. alansidman, if he added the date after the customer wouldn't he be able to just filter for the year then the top 10?

I already added a column to calculate the year then added the year as a filter to my pivot.

Thanks anyway Tank...