conditional rank

[Rossce]

Need to know how to conditional rank based on a criteria

 

[Dannyh1]

straight to the point - i like it

expand your question perhaps

 

[Rossce]

Hi, sorry, I was trying to search for an answer the answer by starting a new thread!

Ok, heres the issue. I have a table with different regions

A1 Region, A2 - A10 Anglia, Midlands, London etc.
B1 Score, B2 - B10 - 99%, 97%, 67%, 78% etc.

So I wanted to put a rank in column C based on criteria from a selector list. In cell D1( the selector), you can choose either Anglia, London etc

Based on this selection, the rank starting in C2 will say, if A2= D1 then rank, if A2=D1 then rank, otherwise blank. So I end up with a rank that changes based on the selection made in D1. I also need it to rank the same score as seperate ranks. As below I put this in, but then realised its ranking the whole lot and not just the Anglia entries plus if there is 2 lots of Anglia at 98% it ranks the same value.

=IF($D$1=$A2,RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1,"")

I want the formula to only rank the Anglia (or whatever selected in D1) plus to rank any scores with the same value for Anglia seperately.

Hope someone can help,

Rossey

 

[anandvarma]

have look at Excel Magic Trick 810 on "excelisfun", youtube channel by Mr. Mike Girvin.

He specifically dealt with RANKIF using Sumproduct function.

totally cool.

 

[Dannyh1]

this thread may help:

http://www.mrexcel.com/forum/showthread.php?t=82479

 

[Rossce]

Hi thanks for the links, I have checked them out, I managed to find this using sumproduct. Problem is, if I have 2 scores the same it ranks them the same number, I need to then rank individually.....