Rank function with exceptions

[Cipher43]

Hello. I am trying to make a carshow judging sheet and I am wondering if this is possible. We judge and give scores for each vehicle for the class they are in. We also have specialty trophies that are judged but not issued scores like the classes are and we only give one trophy per winner so if someone was supposed to get first and is awarded a specialty trophy they just recieve the specialty trophy and need to be removed from the class rankings. I have used vlookup and rank before but is there a way to add a variable to make the specialty winners scores not count toward the class trophies?

 

[Eric W]

Welcome to the forum.

Maybe something like this?

	A	B	C	D	E	F	G
Car	Class	Score	Special Award?	Ranking	Ranking within class
A	4
B
C	X
D
F
G
H	57
I
J
K	1
L
M	X
N
O
P
Q
R
S
T

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]86[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]E[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]73[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(D2<>"","",COUNTIFS($C$2:$C$21,">"&C2,$D$2:$D$21,"=")+1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(D2<>"","",COUNTIFS($C$2:$C$21,">"&C2,$D$2:$D$21,"=",$B$2:$B$21,B2)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]