ranking adjustment with countif formula

[heathball]

{=IF(ISERROR(AA2),DM2,COUNTIFS($DO:$DO,$DO2,AA:AA,"<"&AA2)+COUNTIFS($DO2:$DO2,$DO2,AA2:AA2,AA2))}

Hello, im trying to work out what i can add to this array formula, which will stop the 'equal numbers' appearing with the same duplicate rank number.

For example, it currently works like this

number - rank result from formula
19 - 1
20 - 2
21 - 3
21 - 3
22 - 6
21 - 3
23 - 7

but what i would like it to do is this

number - rank result from formula
19 - 1
20 - 2
21 - 3
21 - 4
22 - 6
21 - 5
23 - 7

if two numbers are equal, i prefer the number at the top (the lower row number) to have the lower number ranking, and for there to be no duplicate ranking results

is this possible to achieve within this formula?

 

[heathball]

{=IF(ISERROR(AA2),AA2,COUNTIFS($DO:$DO,$DO2,AA:AA,"<"&AA2)+COUNTIFS($DO2:$DO2,$DO2,AA2:AA2,AA2))}

this is actually the correct array formula, not the one above

 

[steve the fish]

You dont need array entry for countifs:

=IF(ISERROR(AA2),DM2,COUNTIFS(DO:DO,DO2,AA:AA,"<"&AA2)+COUNTIFS($DO$2:DO2,DO2,$AA$2:AA2,AA2))

The formula just needed a bit of cell absolution.

 

[heathball]

ahh. It works great!
thats interesting.
i thought arrays were involved.

Which part of the formula is achieving the 'no duplicate ranking' part?

is it the "<" ( i cannot find any info on what this is actually doing)
or the $ you added to the $DO$2 and the $AA$2 ?

I guess as you say. its the change in absolutes. I will keep looking at this for a while. Thanks Very much

 

[steve the fish]

This part is doing the no duplicates bit:

COUNTIFS($DO$2:DO2,DO2,$AA$2:AA2,AA2))

It produces 1 for the first time the conditions are met, 2 for the 2nd etc.

 

[heathball]

thank you. So many little things in this game.