Hi there
I've hit a stumbling block with a ranking I am working on which needs to provide an output of a unique rank within the same categories.
I have used sumproduct to achieve this, which works in part as it is able to re-start the ranking system everytime a new ranking is recognised. Unfortunately, within that category there is repetition if the same reference point is picked up more than once. As with a rank / countif statement, I would need these ranks to be unique within the same category.
Below is what I current have
Rank Category Product Lost Sales ($)
2 1397 A 1
1 1397 B 18
5 1473 A 1
4 1473 B 5
5 1473 C 1
1 1473 D 200
5 1473 E 1
3 1473 F 6
=SUMPRODUCT((C$2:C$30=C2)*(E$2:E$30>E2))+1
I have attached an example of what I am working on. For the biggest lost sales within each category, a ranking of '1' would need to be applied and so on.
Many thanks for any help in advance.
Bookmarks