RANK no duplicates problem

[jonastiger]

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?

Thanks in advance
JT

 

[bosco_yip]

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]

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]

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]

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

 

[Peter Bartholomew]

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.