Automatically updating the range for rank function

Question

I want to have a rank function that will automatically update when new data gets pasted into my workbook. The problem is that I have different classes on one sheet and I need to rank items based on the class they belong to.

. The formula I used for the rank was =rank(b2,$b2:$b5) and dragged this down. The reference area changes depending on the animal.

I also have a column that counts the number of times 'dog' was used in a row if that helps. So once 'cat' comes up the counter will reset to 1.

The goal is to have the workbook update automatically so I don't have to change the ranges every month.

Answer 1

Or

=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10>B2))+1

Fill down.

source

Answer 2

Use COUNTIFS():

=COUNTIFS(A:A,A2,B:B,">=" & B2)