Trying my best to rank these two columns, first in descending order by Col. B, then in descending order by Col. C. I am able to find a formula to solve this, but the issue I have is I get duplicates resulting from the second column. I thought to try to add a helper column to bring in the variance between the two columns, but this results in a similar error.

Here is the formula I am using: '=RANK.EQ($B3,$B$3:$B$21) COUNTIFS($B$3:$B$21,$B3,$C$3:$C$21,">"&$C3) IF(D3=0,IF(COUNTIFS($D$3:D3,0)>0,COUNTIFS($D$3:D3,0)-1,0),0)

Wanted table rank is below.

CodePudding user response：

You can delete the variance column if you're only using it to calculate the rank and can change the last part of your formula to count the number of unique combinations up to that point (of 1st and 2nd). It does the same as your formula but for all variances, not just those that equal 0. So it'll see that it is the second occurrence of 0, 50 so adds one to its rank (2nd occurrence minus 1).

=RANK.EQ($B3,$B$3:$B$21) COUNTIFS($B$3:$B$21,$B3,$C$3:$C$21,">"&$C3) (COUNTIFS($B$3:B3,$B3,$C$3:C3,$C3)-1)