Excel

[Iriwel]

Hello. Would appreciate help with the following matter.

I am trying to establish a ranked table based on a certain condition. My table relates to a series of cover designs which have been rated to produce individual scores out of 5. However, I only want to rank those covers that have been rated on 5 or more occasions (as indicated in column 2) such that Cover3 would not be ranked at all and that Cover13 would be ranked #1.

Have tried various approaches with the RANK formula but to no avail. Would therefore be grateful for any suggestions that will help crack the problem.

	Ratings	Score	Rank
Cover1	3	3.33	14
Cover2	12	3.50	11
Cover3	1	5.00	1
Cover4	2	4.50	4
Cover5	9	3.78	8
Cover6	4	2.75	17
Cover7	9	4.44	5
Cover8	15	3.73	9
Cover9	2	3.50	12
Cover10	9	3.67	10
Cover11	1	3.00	15
Cover12	5	3.50	13
Cover13	9	5.00	2
Cover14	14	4.00	7
Cover15	6	3.00	16
Cover16	7	1.00	20
Cover17	6	2.50	19
Cover18	3	4.33	6
Cover19	8	2.57	18
Cover20	3	5.00	3

Thanks in advance.

[#2]

@Iriwel 

In D2:

=IF(B2>=5,COUNTIFS($B$2:$B$21,">=5",$C$2:$C$21,">"&C2)+1,"")

Fill down.

[#3]

Thanks for your reply Hans; it works a treat and I would certainly not have arrived at this conclusion under my own steam. However, one additional matter; I would like the ranking results to be presented as a set of unique numbers. Is there a solution?

Look forward to your reply.

[#4 · Accepted solution]

@Iriwel 

In D2:

=IF(B2>=5, COUNTIFS($B$2:$B$21, ">=5", $C$2:$C$21, ">"&C2) + COUNTIFS($B$2:$B2, ">=5", $C$2:$C2, C2), "")

Fill down.

[#5]

Hans, again my very grateful thanks for your help.

Regards

James