Top 5

billyshears

Board Regular
Joined
Aug 29, 2013
Messages
58
How can I create a little summary in table below that would add the totals for each category and give me a top 5 in order of the most units sold? Sum if does not work because I need to use this on a sheet that is a couple hundred pages long and that changes frequently



CategoryUnits Sold
Apples
3​
Bananas
1​
Apples
2​
Oranges
5​
Grapes
1​
Cherry
7​
Coconut
3​
Cherry
1​
Apples
4​
Bananas
3​
Blueberry
2​
Watermelon
5​
Top 5
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
With Power Query/Get and Transform here is the Mcode

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Units Sold", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Total", each List.Sum([Units Sold]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] <> 6 and [Index] <> 7 and [Index] <> 8)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"
Book1
AB
1CategoryTotal
2Apples9
3Cherry8
4Watermelon5
5Oranges5
6Bananas4
Sheet2


Bring your table into Power Query found on the Data Tab
Group your table by Category
Sort your Grouped Table Descending
Add an Index column and filter out all rows except 1-5

Mcode is the result of these actions.

If you are unfamiliar with this Excel function then click on the link in my signature
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top