hello
this could help you somehow.
for sum function i included round()

for the range of ranks i created named ranges to "shorten" it up a little bit
for duplicates included countif
rank problems.xlsx