Ranking based on criteria

I did not consider the problem. The correction is as follows. It has been clearly marked with colors. I feel that your simulated data still has problems. Please see my solution in column G. You try to change the D14 cell ,The date 2020/5/14 change to 2020/3/14. Pls See result

Originally Posted by wk9128

I did not consider the problem. The correction is as follows. It has been clearly marked with colors. I feel that your simulated data still has problems. Please see my solution in column G. You try to change the D14 cell ,The date 2020/5/14 change to 2020/3/14. Pls See result

Thanks wk9128, your solution works as charm for me
You and Bo_Ry has solved my query efficiently.

Thanks for your responses.