• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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?

Thanks in advance
JT
 

Attachments

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?
 

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.
 
Top