Ranking by category

Sorry for the long-winded title. I work in the grocery retail industry and have a list of items across different categories. I am looking to find out how many categories have a Private Label item ranked first or second in terms of sales. The problem is that a simple rank function doesn't work because there is one category column with many categories listed throughout. So in the "Rank" column, I wouldn't want an item's rank amongst all items in the list but instead its rank among all items with the same value for "Category" (ie a pasta sauce item's rank among only the pasta sauce items).

Any help would be greatly appreciated.

If you have categories in A2:A100 and "scores" in B2:B100 then this formula in C2 copied down will rank by score (highest = 1) in each category.

=SUMPRODUCT((A$2:A$100=A2)*(B$2:B$100>B2))+1

adjust ranges as required

Attached is a an example with fake data. Can you see if this will work?

Thanks a ton.

Hi, I just found this post and it helped me a lot, so thank you very much. However, I would like if you could help me one step further. Just trying to help anyone who see this from google, like me : if you want to use this in a table, here is the rough formula :

Formula:

Please Login or Register  to view this content.

Also, something that was very helpful for me : you can have subcategories too. let's say you want to rank per column 2 inside of the category of column 1, for the sales which are in column 3.

Formula:

Please Login or Register  to view this content.

I hope this makes sense as English is not my first language and this is my first post so the syntax may be way off.