Ranking Dates by two criteria

[sriche01]

Hello All,

So I am trying to rank orders for items in our orders spreadsheet based on when the order was placed:

[TABLE="width: 339"]
<tbody>[TR]
[TD]Date to Ship[/TD]
[TD]Item Number[/TD]
[TD]Order Placed[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]3/4/2019[/TD]
[TD]ABEL MAG18[/TD]
[TD]7/21/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5/13/2019[/TD]
[TD]ABEL MAG18[/TD]
[TD]12/14/2018[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]6/3/2019[/TD]
[TD]AMEL COS10[/TD]
[TD]11/22/2017[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3/25/2019[/TD]
[TD]ARON VIK18[/TD]
[TD]11/19/2018[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]6/3/2019[/TD]
[TD]ARON VIK18[/TD]
[TD]11/22/2017[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5/18/2020[/TD]
[TD]CHAM PAG18[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6/3/2019[/TD]
[TD]CHAM PAG18[/TD]
[TD]8/29/2017[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5/20/2019[/TD]
[TD]CHAM PAG18[/TD]
[TD]6/8/2018[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Above is a sample of the relevant data. Numbers in red are what I am trying to achieve with the lower number meaning the date is older and the order line is higher priority.


The devil is in the details of selecting the data set to rank for each line. I have tried

{=RANK.EQ(C2,IF($B$2:$B$9=B2,IF(YEAR($A$2:$A$9)=YEAR(A2),$C$2:$C$9)),1)}

It evaluates appropriately until the final step, then gives me a #VALUE error.

Any Ideas?

Thanks!

 

[steve the fish]

Does this do it?

=COUNTIFS(B:B,B2,C:C,"<"&C2)+1

Im not sure why your last 3 rankings are 1, 1, 2?

 

[sriche01]

Thank you! I had seen other solutions with countifs, but couldn't quite wrap my mind around it. Now I do. As for the discrepancy you mentioned, I wasn't very clear. I had two criteria.
1) the date the order was placed
2) the year it was to ship
I want to rank the orders for 2019 separately from 2020. Sorry I didn't explain that! But I just added some conditions on the countifs to accommodate that and voila!

Thanks Again!