# RANK no duplicates problem

#### jonastiger

##### Member
Hi I'm stucked with this issue:
I have a table data and I need to rank data based on date criteria for each code (see attached file please).
Using =COUNTIFS([COD];[@COD];[DATE];">"&[@DATE])+1 returns rank 1,1,1,4,4,6,... and what I want is 1,2,3,4,5,6,...
what's the change in formula to avoid duplicates?

JT

#### Attachments

• 10.8 KB Views: 7

#### bosco_yip

##### Excel Ninja
In D3, formula copied down :

=COUNTIFS(C\$3:C\$137,C3,B\$3:B\$137,">"&B3)+COUNTIFS(C\$3:C3,C3,B\$3:B3,B3)

#### jonastiger

##### Member
Hi
Thank you for your response. But I still have an issue:
=COUNTIFS(C\$3:C\$137,C3,B\$3:B\$137,">"&B3)+COUNTIFS(C\$3:C3,C3,B\$3:B3,B3)
How do I represent the red ranges above in a table (as shown in the attached), since the formula appears like this:
=COUNTIFS([COD],[@COD],[DATE],">"&[@DATE])+COUNTIFS([COD],[@COD],[DATE],[@DATE]) ?

#### jonastiger

##### Member
Hi
I wonder how this formula works:
=COUNTIFS(C\$3:C\$137,C3,B\$3:B\$137,">"&B3)+COUNTIFS(C\$3:C3,C3,B\$3:B3,B3)
in a table:
=COUNTIFS([COD],[@COD],[DATE],">"&[@DATE])+COUNTIFS([COD],[@COD],[DATE],[@DATE]) ?

How to block C\$3 or B\$3?

#### jonastiger

##### Member
Found the solution
=COUNTIFS([COD],[@COD],[DATE],">"&[@DATE])+COUNTIFS(C\$3:C3,[@COD],B\$3:B3,[@DATE]) ?

#### Peter Bartholomew

##### Well-Known Member
Seems to work. My normal strategy with duplicate records is to add a sequence as record numbers. It would be easy if COUNTIFS etc, were generalised to accept arrays as well as range references but at least dynamic arrays will allow such a helper range to resize to match the data table.