I've been trying to rank the votes of the players for each team using functions like RANK and INDIRECT (because there are many teams and I'd like to calculate the rank for each team - then later maybe also for role within the team) but I'm having a hard time.

I've found a way to work around the problem of the duplicate rank values, but only if I use a fixed range, which I want to avoid because I have 10 Teams and I'd like to calculate the rank for each team without typing each range.

Here below is my table (the highlighted rows are the ones with duplicated values):

**Col H and I**: start and end range calculated for each Team

**Col T**: the reference vote that I'm using to establish the rank;

**Col X**: rank calculated with a fixed range (the results in this column are the ones I'd like to get)

```
=IFERROR(RANK(T2;$T$2:$T$26;0)+COUNTIF($T2:$T$2;T2)-1;0)
```

**Col W**: rank calculated using a variable range (with function INDIRECT). Unless there's a typo (which I can't see) it should be equal to the previous result, but it's not and doesn't manage the duplicate values

```
=IFERROR(RANK(T2;INDIRECT("$"&"T"&"$"&H2&":"&"$"&"T"&"$"&I2);0)+COUNTIF(INDIRECT("$"&"T"&H2&":"&"$"&"T"&"$"&H2);T2)-1;0)
```

**Col Y and Z**: tried using the SUMPRODUCT function with fixed and calculated range. The result at least is the same but it's wrong (not what I'd like)

```
=SUMPRODUCT((C2=$C$2:$C$26)*(T2<$T$2:$T$26))+1
=SUMPRODUCT((C2=INDIRECT("$"&"C"&"$"&H2&":"&"$"&"C"&"$"&I2))*(T2<INDIRECT("$"&"T"&"$"&H2&":"&"$"&"T"&"$"&I2)))+1
```

Can anybody tell me how to fix the formula in column W or how to obtain the result in a different way?

`=SUMPRODUCT((C2=$C$2:$C$27)*(T2>$T$2:$T$27))*(T2>=$H$2:$H$27)*(T2<=$I$2:$I$27)+1`

and in X2:`=RANK(T2,$T$2:$T$27,1)+COUNTIF($T$2:T2,T2)-1`

`=ABS(SUMPRODUCT((C2=$C$2:$C$27)*(T2>$T$2:$T$27))*(T2>=$H$2:$H$27)*(T2<=$I$2:$I$27)-1)`

and in Cell Z2:=`RANK(T2,$T$2:$T$27,0)+COUNTIF($T$2:T2,T2)-1`

1more comment