# Excel: calculate rank avoiding duplicated values and using a calculated range

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?

• Since you have tried so many formula and still away from the expected results so better edit you post & share the expected RANK's order will help us to fix it !! Apr 11 at 5:25
• @RajeshS: but I did write it in my post. The rank I'd like to obtain is the one in column X (no duplicates in rank) but I need to obtain this result using a calculated range because my table is 10.000 rows long (average of 25 rows of players per Team * 10 Teams * 38 days of season) and I don't want to type each range (Team 01 T2:T26, Team 02 T27:52 and so on) manually. Hope this clarifies Apr 11 at 9:06
• ,, once U try this in cell W2: `=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` Apr 12 at 6:33
• in Cell Y2: `=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` Apr 12 at 6:37

I think you have a mistake in your formula

``````=COUNTIF(INDIRECT("\$"&"T"&H2&":...
``````

Should be

``````=COUNTIF(INDIRECT("\$"&"T"&ROW()&":
``````
• Also note with 10000 rows, your spreadsheet may become very slow with that many volatile functions like indirect because excel will recalculate all the volatile cells with any minor change. You might want to define your dynamic range rather with non-volatile index like so `INDEX(\$T:\$T,H2):INDEX(\$T:\$T,I2)` Apr 11 at 10:19
• Its a bit lengthy, but you can use the following non-volatile formula to index into any cell in any sheet `=INDEX(CHOOSE(*sheetnumber*,Sheet1!\$A:\$ZFD,Sheet2!\$A:\$ZFD,Sheet3!\$A:\$ZFD),*row number*,*column number*)`. You must extend this formula to include every sheet in your workbook. The order that you place them in determines the sheetnumber that CHOOSE will return. Apr 11 at 19:29
• You can also define that lengthy INDEX(CHOOSE(...)) function as a named cell or range, so that you don't have to type it out in several cell formula (and if you have to edit it/add a new sheet, you only have to edit the defined name once). You can use relative define names to add the variables for sheet, row and column numbers to the named range in adjacent cells, or in Office 365 define the name using the lambda function `=LAMBDA(sheetnum, r, c, INDEX(CHOOSE(sheetnum,Sheet1!\$A:\$ZFD,Sheet2!\$A:\$ZFD,Sheet3!\$A:\$ZFD),r,c)`, so you can use the name range like a function SHEETINDEX(1,1,1). Apr 11 at 19:50
• Finally, its worth noting that INDEX does not only return the cell value, but a reference to a cell - which is why we can define a range like before INDEX(...):INDEX(...). Likewise we can define our named range e.g. SHEETINDEXRANGE to return a range `=LAMBDA(sheetnum, r, c, r2, c2 INDEX(CHOOSE(sheetnum,Sheet1!\$A:\$ZFD,Sheet2!\$A:\$ZFD,Sheet3!\$A:\$ZFD),r,c):INDEX(CHOOSE(sheetnum,Sheet1!\$A:\$ZFD,Sheet2!\$A:\$ZFD,Sheet3!\$A:\$ZFD),r2,c2))`. SHEETINDEXRANGE(1,2,1,10,3) will effectively return Sheet1!\$A\$2:\$C\$10 Apr 11 at 20:01
• You are four excel articles away from using these formulas. Just google "how to use choose in excel", then "how to use choose and index to index into different sheets", then "exceljet named ranges in excel" and finally "how to use lambda in excel 365". Your spreadsheet will be super fast and you will be an excel guru in 10 minutes. Good luck! Apr 14 at 21:12