Rank in Excel without skipping duplicates

Question

Hi there,

I am trying to rank individual profiles by their year of enrollment. For instance, I might have one student who has a row for each year of enrollment. However, some years, students have two rows for two field areas. I want to be able to rank their years of enrollment without skipping duplicates so that even if they have two rows from the same first year (1 and 1), their next row will be 2.

I'm using this function:

=COUNTIFS($A$2:$A$20409,A2,$Q$2:$Q$20409,"<"&Q2)+1

A is the student ID and Q is the year. How do I make it so that the year enrolled will not skip duplicates?

Answer 1

=SUM(IF(UNIQUE(FILTER($Q$2:$Q$20409,$A$2:$A$20409=A2))<=Q2,1,0))

Answer 2

=COUNTIFS($A$2:$A$20409,A2,$Q$2:$Q$20409,"<"&Q2)+COUNTIFS(A2:$A$2,A2,Q2:$Q$2,Q2)

Answer 3

It seems like this is getting closer, but I want it to show the same ranking if the year is duplicated. For ID 1019, 2015 and 2015 would both have 1 and 1, but 2017 would have 2, 2018 would have 3 and 3.