how can i compare two values and rank them in excel

Question

how can i compare two values and rank them in excel the largest value should be 1 and smallest be last

Category    amount  rank
abc            300     1
abc              6     3
abc             34     2
xyz             50     2
xyz            568     1
xyz              1     3

I tried this by my own COUNTIFS($A:$A,$A2,$B:$B,">"&$B2)+COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2) its working but I want it in more simpler way.

Answer 1

Try this:

=SUM((B2<=$B$2:$B$15)*1*(A2=$A$2:$A$15))

This is an array formula so commit by Ctrl+Shift+Enter