Rank/Order including manual overriding rank/order input

[77highland]

Hi All,

Need some help please...

I need a formula to return a final rank/order based on date range and manual rank/order override.

I have attached a sample sheet which includes expected results and more explanation.

Any help will be greatly appreciated.

[Bo_Ry]

Please try at G5

=IF(E5,E5,D5+SUMPRODUCT(COUNTIFS(D5,">="&$E$5:$E$14))-COUNTIFS($E$5:$E$14,"<>",$D$5:$D$14,"<"&D5))

[77highland]

@Bo_Ry

Unfortunately whilst this formula works with 1 manual order input it returns duplicate values in >=2 manual order cells are populated. Sorry I should have made this requirement clear in my original post

[JeteMc]

The following may do what you want.
This proposal employs 11 helper columns, one to convert the values in column C into dates at midnight and one for each entry in column C to account for the manually overridden rankings.
Column G converts the dates in column C using: =INT(C5)
Columns H:Q are populated using:

Please Login or Register  to view this content.

The final ranking in column R is populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.

[77highland]

@JeteMc

Your solution works. Thank you very much for your help.

Thread marked as solved and reputation added

[JeteMc]

You're Welcome. Thank You for the added reputation and for marking the thread as 'Solved'. I hope that you have a blessed day.