# Thread: Must be a formula (array?) that can do this - but I can't figure it out

1. ## 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
Fajitas
Sandwich

and in the second column,

Bob
Pizza
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?

2. 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```

3. ## The Following User Says Thank You to Maudibe For This Useful Post:

psymann (2014-12-09)

4. In A2:
=IF(C2="",0,SUMPRODUCT((\$D\$25:\$P\$32=C2)*\$C\$25:\$C\$3 2))
and copy down.

5. ## The Following User Says Thank You to rory For This Useful Post:

psymann (2014-12-09)

6. 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 :-)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•