Results 1 to 4 of 4

20141207, 20:09 #1
 Join Date
 Dec 2014
 Location
 Perth, Australia
 Posts
 2
 Thanks
 3
 Thanked 0 Times in 0 Posts
Must be a formula (array?) that can do this  but I can't figure it out
Hello all,
I have a spreadsheet, attached, where I have a grid of data.
Each column represents a person, and each person has listed their favourite foods.
So in the first column, I have:
Alice
Curry
Lasagne
Pizza
Toad in the Hole
Fajitas
Sandwich
and in the second column,
Bob
Pizza
Toad in the Hole
Cottage Pie
Sandwich
Lasagne
Gammon and Egg
Chocolate Cake
Carol
Shepherd's Pie
Pizza
Curry
and so on.
There are a number of different foods, and each one might be chosen by Alice, Bob, or one of the other people in the other columns.
I can easily count up how many times, overall, they vote for Curry  for Alice, Bob and Carol, above, two of them had Curry listed somewhere, so the answer is 2.
But what I want is to add a weighting to these. So the top answer on each person's list is worth 6, the second one worth 5, then 4, 3, 2 and anything after that worth 1.
I then want to create a 'score' for Curry. It would be:
Alice  voted it #1  6 points
Bob  didn't vote for it at all  0 points
Carol  voted it #3  4 points
Overall score for Curry: 10 points.
And then I want to work out the same for Pizza and all the other foods.
I sort of figure I ought to be able to do this all in one cell, using some sort of array formula. So that I have:
Food  Score
Curry  10
Pizza  15
etc
I just can't figure out how to do it, other than by using a really annoying long one that does a separate calculation for each row, and adds them all together.
I have attached the file; my bad formula is in column A in the yellow cells, and does get the right answer... but does anyone know how to rewrite it neatly and concisely so that if I had 800 rows instead of 8, it would still cope?

20141207, 22:32 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,559
 Thanks
 111
 Thanked 620 Times in 565 Posts
psymann,
An easy way to do this is with a user defined function (UDF). Enter the following code in a standard module then in cell A2 enter the following formula and copy down to A23:
=ScoreSum(C2)
HTH,
Maud
foodchart1.png
Code:Public Function ScoreSum(food As String) As Integer ' 'DECLARE AND SET VARIABLES Dim rng As Range Dim cell As Range Dim weighting As Integer weighting = 0 ' 'MATCH FOOD WITH EACH ROW IN FOOD CHART (D25:P32) If food = "" Then Exit Function For I = 25 To 32 Set rng = Range("D" & I & ":P" & I) ' 'MATCH FOOD WITH EACH ROW IN FOOD CHART (D25:P32) AND INCREMENT SCORE For Each cell In rng If cell = food Then weighting = weighting + Cells(I, 3) Next cell Next I ' 'RETURN SCORE ScoreSum = weighting End Function

The Following User Says Thank You to Maudibe For This Useful Post:
psymann (20141209)

20141208, 02:38 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
In A2:
=IF(C2="",0,SUMPRODUCT(($D$25:$P$32=C2)*$C$25:$C$3 2))
and copy down.Regards,
Rory
Microsoft MVP  Excel

The Following User Says Thank You to rory For This Useful Post:
psymann (20141209)

20141209, 18:20 #4
 Join Date
 Dec 2014
 Location
 Perth, Australia
 Posts
 2
 Thanks
 3
 Thanked 0 Times in 0 Posts
Rory  that was just what I was after  I'd tried a few things that were tantalisingly close, but I'd not really got the ()*() syntax right inside the SUMPRODUCT formula, and had tried to do it with a comma instead of an asterisk, thinking the formula would do the product bit itself.
Maudibe  I'd not even contemplated a UDF as I'd rather forgotten they were possible. And that will almost certainly help with some other upcoming problems to solve too, so also useful :)
Thanks both :)