Rank data based on two columns

Hi everyone,

I was trying to do a more advanced ranking for two columns A and B. Both columns are numeric values and there are duplicates.

The idea is trying to sort data by column A and then by column B. The formula that I used is:

=IF((A1="")+(B1=""),"",COUNTIF($A$1:$A$20,">"&A1)+COUNTIFS($A$1:$A$20,A1,$B$1:$B$20,">"&B1)+COUNTIFS($A$1:A1,A1,$B$1:B1,B1))

Please see the sample file here. The trouble is: the ID = 10 is not there. So why the formula failed to give a complete ranking order?

Many thanks.

Many thanks, benishiryo. It worked perfectly