Exclude cell value from rank range based on value in another cell

Question

I am putting together a golf society scoring & handicap sheet. I am having trouble trying to exclude a NON-members score from the ranking of the members scores.

A non member cannot win, even if they have the highest score. I want to ignore/exclude the NON-members scores from the result positions. i.e. If a NON-member has the highest score, they will not be ranked as 1st position (or any position for that matter)

So here's the deal:

A NON-member is marked with "n". I want to see if the "Members" column cell has an "n" in it or not. If so, I want to exclude their "Score" form being included in the overall "Position" column.

Here's what I have so far:

=IF(AND(C2="",D2<>""),RANK(D2,$D$2:$D$39,0),IF(AND(C2="",D2=""),"",IF(AND(C2<>"",D2=""),"",IF(AND(C2<>"",D2<>""),""))))

Here is the result that I am getting:

But here is the result that I want:

The difference between the 2 tables is the values in the "Position" column. I only circled one difference but if you look closely you will notice that the values in the "Position" column are all different in both tables. This is due to the NON-member scores being excluded from "Position" ranking in the second table. The second table is the result I would like to achieve.

Solution

Here's a formula that I modified from this answer after searching for "Excel rank with conditions":

=IF(B2<>"n",COUNTIFS(B$2:B$100,"<>n",A$2:A$100,">"&A2)+1,"")

Note that this works in Excel 2007 or higher.

Comments

[Derrik]

This does not correctly answer the OP's question. While this formula does sort the results and excludes non-members, it will do so in ascending order instead of descending order.

[Doug Glancy]

@Derrik, right you are. I edited and changed the "<" to a ">".

[PhillyStafford]

Thanks for your help @Derrik & DougGlancy I was able to figure out your formula and modify it to my needs. I had to put in a condition to exclude someone who hadn't played from getting a +1 on their handicap. I ended up with this formula: Should I add this formula as an answer to my question? I'm new to StackOverFlow so I'm not up to speed with the etiquette =IF(OR(D2="",D2=0),"",IF(C2<>"n",COUNTIFS(C$2:C$39,"<>n",D$2‌​:D$39,">"&D2)+1,""))

[Doug Glancy]

You're welcome. It sounds like I answered your question as posted, or close to it, so accepting it is the right move. Thanks.

[PhillyStafford]

:) No problem! Thanks for your help. I was having trouble wit that for a while