How do I compare two values ​​and classify them in Excel?


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.

Try this:


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