Top 5

[billyshears]

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

Category	Units 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

 

[alansidman]

With Power Query/Get and Transform here is the Mcode

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
	A	B
1	Category	Total
2	Apples	9
3	Cherry	8
4	Watermelon	5
5	Oranges	5
6	Bananas	4
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