Find the most frequent but weighted values in Excel for a voting system calculator

Question

I'm looking to create a voting system calculator, by taking a list of people's preferences (1st to 5th preference), and automatically find the most popular ones, but by giving weight to the place of the user's preference. i.e 1st Preference gets 5 points, 2nd Preference gets 4 points, 3rd preference gets 3 points etc. Then add up the points values, and show the top 6 most popular ones based on this voting system.

I have followed this post for the most frequent numbers... but it's the weighting/boosting of values that I'm missing out on. In my example, I will only have 4 or 5 people voting, and I have roughly 25 possible values.... so for now, the alphabet works pretty well. Here is an example of the results that I'm after.

*The results show the aggregate points of the most popular preferences, and the most popular preferences themselves in the two rows. Now I manually figured out these results in a separate two columns on the side, but ideally I just get this all within the cells V3 to AA4...if that's possible, however I'm open to any better alternatives.

Here's my manual column calculation list to show how I know the preferences:-

Answer 1

Just use SUMIF:

My formula in Y4:

=SUMIF($B$4:$U$4;X4;$B$3:$U$3)

Piece of cake.

Besides, if you got E365, with functions like UNIQUE and SORT probably you could do the whole list with formulas. In my example, Excel 2007, I had to manually input values A to Z.

Answer 2

I ended up doing this by shifting all of the calculations off to another sheet, having one sheet per 'item being voted on'. Then taking the results from there and putting them back into the 'Results' cells for each item.

This allowed me to have rows for items being voted on, while 'hiding' the calculations away on the other sheets.

Thanks to @foxfire-and-burns-and-burns, on the other 'calculation' sheet I used SUMIF, referencing the main data sheet.

=SUMIF(MainVotesSheet!$B$4:$U$4;X4;MainVotesSheet!$B$3:$U$3)

Then I added some calculations to figure out the Ranks, Score and which Option got that Score.

Where, 'Score Result' is

=LARGE(B$2:B$29,D3)

where D3 is the Rank number I'm after.

And 'Option' will find that one in the list of SUMIFs using an index to match. This equation also allows for Options that have equalling votes.

=IF(E2>0, INDEX(A$2:A$29, MATCH(1, (B$2:B$29=LARGE($B$2:$B$29,D2)) * (COUNTIF(F$1:F1, A$2:A$29)=0), 0)), "")

Then I have the results I'm after, but because I wanted to move it into a single row, I go back to my 'MainVotesSheet', and reference these cells.

1st Place Score

=Item1Calcs!$E$2

1st Place Option

=Item1Calcs!$F$2

Now, the only thing left is adding rows for each Item to Vote on.... In which case, Calculation Sheets needed to be duplicated, with the SUMIF referencing the appropriate row from the MainVotesSheet. And then, each of the Results in the MainVotesSheet needed to reference it's corresponding Calculations Sheet.

This was a bit of a hassle manually updating it... and I'm sure it's not the cleanest solution, but it works, and will hopefully help anyone looking to do something similar.