Using single formula to list unique items, count and sort numerically by descending order and then a-CodePudding

As title says I need to use a single formula for listing unique items from raw list in a column, count them and sort them numerically by descending order and then alphabetically for items with same count, finally also excluding some words like "ITEMS".

This should help understand:

Is this doable? I did this with a complicated formula for the sorting on a left column then on the righ each cell with a COUNT formula but it's not elegant, not efficient and for some reason some items were not counted correctly. I tried combinations of QUERY, UNIQUE, ARRAYFORMULA, COUNT etc but can't get it to work like I want.

Dummy file:

CodePudding user response：

I doubt it's Excel you're looking for, but this will work in Excel (Office 365):

=LET(a,B4:B39,
     u,UNIQUE(a),
     c,COUNTIF(a,u),
SORT(CHOOSE({1,2},u,c),{2,1},{-1,1}))