Calculating points for lists of films

[davehedgehog]

I can't seem to work out an easy way to do this. We are collecting lists of favourite films from some forum members (at least 30 people so far). The lists can be any length, but the point system is 1000 for #1, 1 for #1000 and 0.5 for any after that. How can I calculate the number of points each film gets? I have the sheet set up so that A contains rank #, B has # points and then others are the film lists.

[JieJenn]

Can you post a sample file?

[dilipandey]

Hi davehedgehog,

Welcome to the forum.
Though a sample workbook would helped the forum members to help you quickly, I have tried designing what you have laid down, please go through the attached and let us know if this is what you were referring. Also would appreciate if you can put your results manually in some cells, which you wanted to achieve through formulas. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[davehedgehog]

Hi, sorry about not attaching the worksheet, a forum I used to use didn't allow that. I've attached it to this post. Dilipandey, I don't see anything attached...

[dilipandey]

Hi Dave,

Sorry I missed the attachment in my previous post, but no worries, I found your post later and tried solving your issue. See the attachment and also my comments. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[davehedgehog]

Hi, I'm not sure you understand what I was asking for.. Each persons individual ranked lists were in Sheet 1. I manually copied all the films into one column in Sheet 2 and filtered so it was only unique records. The #votes column was just so I could see how many people had voted for that particular film. But the points were to be based on each persons ranking.

I actually updated the spreadsheet after I attached it, so I'll upload my new one. As an example, I chose The Matrix. I highlighted its 4 occurrences and the number of points it should get from those rankings is 997 + 919 + 915 + 846, with a total of 3677 points.

[dilipandey]

Hi Dave,

Thanks for clearing my doubt(s).
See the attached file for your desired results. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[JieJenn]

Post deleted

[XLent]

Alternatively

Sheet2!C2:
=SUMPRODUCT((Sheet1!$C$2:$N$1001=$A2)*Sheet1!$B$2:$B$1001)
confirmed with Enter
copied down

[davehedgehog]

Thanks a lot guys Was really frustrating me, but it works perfectly now.

[dilipandey]

You are welcome Dave...

Cheers

and.. get ready to welcome New Year... wish you the best.

Regards,
DILIPandey

<click on below 'star' if this helps>