Rank Arrayformula does not work with COUNTIF

Question

I'm using this formula in Google Sheets to RANK rows based on TOT column. It works well if I put the formula in E2 and drag over the column, but I want to put this formula inside an arrayformula and it's not working. How could I arrayformulize this?

=RANK.EQ(I3;$I$3:$I;0)+COUNTIF($I$3:I3;I3)-1

=ARRAYFORMULA(RANK.EQ(I3;$I$3:$I;0)+COUNTIF($I$3:I3;I3)-1) This doesn't work

https://docs.google.com/spreadsheets/d/1D0ralOL6nv5Cv_L5La4x8q5XrlX56oVFJHJqgz-aj94/edit

Note: the CountIf part is important because I don't want duplicated ranking numbers if there's a draw between two rows. Without COUNTIF the arrayformula works well but I don't want to remove it.

Answer 1

use:

=INDEX(SORT({SORT(ROW(D2:D); D2:D; )\ROW(D2:D)-1});;2)

---

update

=SORT(ROW(D2:D)-1; SORT(ROW(D2:D); D2:D;;C2:C;); 1)

or:

=SORT(ROW(D2:D)-1; SORT(ROW(D2:D); D2:D;;C2:C; 1); 1)

Answer 2

The sheet you've shared doesn't match the formula you have.

Here's a formula that works with the sheet you've shared:

=ARRAYFORMULA(RANK.EQ(D2:D;D2:D;0)+COUNTIFS(D2:D;D2:D;ROW(D2:D);"<="&ROW(D2:D))-1)

When using ArrayFormula you have to provide ranges to the functions parameters (and you don't need absolute references since you aren't going to drag the formula).

In this case it was slightly more complex as that COUNTIF you have must be done with a COUNTIFS.

Answer 3

You can try this approach. It sorts the sequence of numbers (1 to 14 in this case) according to the ascending order of D2:D; and then finds out with MATCH in which order they were left:

=ARRAYFORMULA(MATCH(SEQUENCE(COUNTA(D2:D));SORT(SEQUENCE(COUNTA(D2:D));FILTER(D2:D;D2:D<>"");0);0))