SUMPRODUCT function using columns that match criteria across different sheets

[DotAGenius]

Hi all!

First time posting here.

I am trying to produce a spreadsheet to see an array of "Weighted Scores" based on different criteria.
If anybody plays FIFA, this might be easier to understand.

Players have "Attributes" like Shooting, Passing, Defending, and Dribbling.
You can assign "Styles" to players that give bonuses like +15 to Shooting, +20 to Defending, or +10 to both Shooting and Passing, etc.
I also created "Archetypes," such as a "Wing Player" who would benefit from having good Shooting and Passing, but not so much from Defending.

On the first sheet, I have a list of Players and all their Attributes. For example, it would look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Player A[/TD]
[TD]Player B[/TD]
[TD]Player C[/TD]
[TD]Player D[/TD]
[/TR]
[TR]
[TD]Shooting[/TD]
[TD]80[/TD]
[TD]70[/TD]
[TD]90[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Passing[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]70[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Defending[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Dribbling[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

On the second sheet, I list out what the Styles do. For example, it would look like this, to indicate what bonuses each Style gives (e.g. applying Style Z to a Player would give them +20 to Dribbling):
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Style W[/TD]
[TD]Style X[/TD]
[TD]Style Y[/TD]
[TD]Style Z[/TD]
[/TR]
[TR]
[TD]Shooting[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Passing[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Defending[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Dribbling[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


On the third sheet, I list out the Archetypes and how important different Attributes are for each respective Archetype (out of 1.00). For example, it would look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Archetype 1[/TD]
[TD]Archetype 2[/TD]
[TD]Archetype 3[/TD]
[TD]Archetype 4[/TD]
[/TR]
[TR]
[TD]Shooting[/TD]
[TD]0.5[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Passing[/TD]
[TD]0.5[/TD]
[TD]0.65[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Defending[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]Dribbling[/TD]
[TD]0[/TD]
[TD]0.35[/TD]
[TD]0.5[/TD]
[TD]0.2[/TD]
[/TR]
</tbody>[/TABLE]

Phew, ok! So now that all that's out of the way, this is what I hope to accomplish.

I want to be able to quickly determine the best Style/Archetype combination for every Player based on Weighted Scores for each combination. So far, I have been manually selecting each Archetype and each Style and comparing them using drop-down lists and SUMPRODUCT. For example, Player A with Style W equipped and playing as Archetype 1 would be the SUMPRODUCT of (the Array under Player A + Array under Style W (to get total Attributes)) * (the Array under Archetype 1) to get one Weighted Score. In this case, it would be [95,80,20,45] * [0.5,0.5,0,0] to get a Weighted Score of 241.

On my fourth sheet, this is what I have (with A1 being a drop-down list of all Players):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Player 1[/TD]
[TD]Archetype 1[/TD]
[TD]Archetype 2[/TD]
[TD]Archetype 3[/TD]
[TD]Archetype 4[/TD]
[/TR]
[TR]
[TD]Style W[/TD]
[TD]241[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to see the Weighted Score per each Style/Archetype combination, so I can quickly determine how this Player is best utilized.
The problem I am facing is finding a way to locate the Attributes for the Player selected from the list in A1, and then using that as the first part of the first Array.
I tried to do some sort of SUMPRODUCT function with MATCH, but I couldn't figure out how to do it in a robust manner.

If anybody has a solution, I would be extremely grateful!

Thank you in advance!

 

[Marcelo Branco]

Welcome to Mr Excel forum

I don't understand how did you get 241 for Player A, Style W, Archetype 1 (???)

Shouldn't it be
[95,80,20,45] * [0.5,0.5,0,0] =
95*0.5 + 80*0.5 + 20*0 + 45*0 =
47.5 + 40 + 0 + 0 = 87.5

Could you clarify?

M.

 

[DotAGenius]

Hello Marcelo,

Yes you are right, that was a typo. The Weighted Score should be, as you said, 87.5.

Thanks!

 

[Marcelo Branco]

Assuming Tables 1, 2 and 3 are in columns A:E of their respective sheets, headers in row 1, try this

Sheet4

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Player A​

[/TD]
[TD]

Archetype 1​

[/TD]
[TD]

Archetype 2​

[/TD]
[TD]

Archetype 3​

[/TD]
[TD]

Archetype 4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Style W​

[/TD]
[TD]

87,5​

[/TD]
[TD]

67,75​

[/TD]
[TD]

70​

[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Style X​

[/TD]
[TD]

85​

[/TD]
[TD]

74,25​

[/TD]
[TD]

62,5​

[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Style Y​

[/TD]
[TD]

80​

[/TD]
[TD]

66​

[/TD]
[TD]

60​

[/TD]
[TD]

36​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Style Z​

[/TD]
[TD]

80​

[/TD]
[TD]

73​

[/TD]
[TD]

70​

[/TD]
[TD]

28​

[/TD]
[/TR]
</tbody>[/TABLE]

Player A in A1

Formula in B2 copied across and down
=SUMPRODUCT(INDEX(Sheet1!$B$2:$E$5,0,MATCH($A$1,Sheet1!$B$1:$E$1,0))+INDEX(Sheet2!$B$2:$E$5,0,MATCH($A2,Sheet2!$B$1:$E$1,0)),INDEX(Sheet3!$B$2:$E$5,0,MATCH(B$1,Sheet3!$B$1:$E$1,0)))

Hope this helps

M.

 

[DotAGenius]

Hi Marcelo,

I am excited because this is very close to what I'm looking for and I understand the concept. However, I am getting the error message: Function ADD parameter 1 expects number values. But 'Player A' is a text and cannot be coerced to a number.
Should I be offsetting all the rows by 1?

Much thanks!

 

[Marcelo Branco]

The formula worked perfectly for me.
Have you checked the sheet names (Sheet1, Sheet2, Sheet3)?
Also check the data location on each sheet - columns A:E, headers i row 1.

I didn't understand the error message you received. Could you tell us exactly the wording of such message?

M.

 

[DotAGenius]

Hi Marcelo,

Turns out I had accidentally set the start of the arrays for the sumproduct at B1 instead of B2. I just changed those and it worked, thanks again!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.

 

[DotAGenius]

Hi Marcelo (or anybody),

I've run into an issue that I overlooked.
So when adding up the original Player's Attributes from Table 1 and the additional Attributes from the Styles in Table 2, how can I put an upper limit of 99 for the SUMPRODUCT?

So if Player A had 90 in Shooting instead of 80, the SUMPRODUCT should not be [105,80,20,45] * [0.5,0.5,0,0] but rather [99,80,20,45] * [0.5,0.5,0,0].

Thank you in advance!

 

[Marcelo Branco]

Hi Marcelo (or anybody),

I've run into an issue that I overlooked.
So when adding up the original Player's Attributes from Table 1 and the additional Attributes from the Styles in Table 2, how can I put an upper limit of 99 for the SUMPRODUCT?

So if Player A had 90 in Shooting instead of 80, the SUMPRODUCT should not be [105,80,20,45] * [0.5,0.5,0,0] but rather [99,80,20,45] * [0.5,0.5,0,0].

Thank you in advance!

To attend this new requirement you need a more complex formula.

Try
Array formula in Sheet4 B2 copied across and down
=SUM(IF(INDEX(Sheet1!$B$2:$E$5,0,MATCH($A$1,Sheet1!$B$1:$E$1,0))+INDEX(Sheet2!$B$2:$E$5,0,MATCH($A2,Sheet2!$B$1:$E$1,0))>99,99,INDEX(Sheet1!$B$2:$E$5,0,MATCH($A$1,Sheet1!$B$1:$E$1,0))+INDEX(Sheet2!$B$2:$E$5,0,MATCH($A2,Sheet2!$B$1:$E$1,0)))*INDEX(Sheet3!$B$2:$E$5,0,MATCH(B$1,Sheet3!$B$1:$E$1,0)))

confirmed with Ctrl+Shift+Enter, not just Enter

M.