Automated Indices

[Jwtully]

Hello All,

I’ve recently been given the task to create a pseudo-automated system that can have tools in a list output a user experience index based on a data set.

As of right now, I’ve been grabbing the data manually and doing the math through a number of SUMIFS and AVERAGEIFS. You can imagine, this system is time intensive and not scalable to any degree. Especially with future request indices reaching nearly 25+ tools.

My current assumption is to use an Index with a small min + 1 row equations then pile some my SUMIFs and AVERAGEIFs on top, but I’m thinking that’s to heavy.

Below is the dummy dataset as an example.

The Index equation is:

(Sum Respondent Tool Score / Sum Current Role Tool Score)	X 100
(Sum Client Tool Score / Sum Total Tool Score)

<tbody>
</tbody>

Below is the variable list I’d like the array to grab from.
[TABLE="width: 0, align: center"]
<tbody>[TR]
[TD]Variable Index 1
[/TD]
[TD]Variable Index 2
[/TD]
[/TR]
[TR]
[TD]Tool 1
[/TD]
[TD]Tool 1
[/TD]
[/TR]
[TR]
[TD]Tool 2
[/TD]
[TD]Tool 3
[/TD]
[/TR]
[TR]
[TD]Tool 4
[/TD]
[TD]Tool 5
[/TD]
[/TR]
</tbody>[/TABLE]


Below is the current dataset structure.
[TABLE="width: 0, align: center"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Respondent ID
[/TD]
[TD]Current Role
[/TD]
[TD]Client
[/TD]
[TD]Variable Index 1
[/TD]
[TD]Variable Index 2
[/TD]
[TD]Tool 1
[/TD]
[TD]Tool 2
[/TD]
[TD]Tool 3
[/TD]
[TD]Tool 4
[/TD]
[TD]Tool 5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6390901863
[/TD]
[TD]Manager
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]6393811019
[/TD]
[TD]Director
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]6399578106
[/TD]
[TD]Analyst
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]6401731956
[/TD]
[TD]Manager
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]6400323399
[/TD]
[TD]Sr. Analyst
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]6406122762
[/TD]
[TD]Sr. Analyst
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]6406201649
[/TD]
[TD]Manager
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]6390869805
[/TD]
[TD]Analyst
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]6406150883
[/TD]
[TD]Director
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]6408579156
[/TD]
[TD]Director
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]TOTAL
[/TD]
[TD]TOTAL
[/TD]
[TD]TOTAL
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[/TR]
</tbody>[/TABLE]

*values are respondent’s experience in that tool.

Any help is appreciate, and if I’ve left anything out please let me know.

Thanks for your time!

Jwtully

 

[DRSteele]

Welcome to the forum.

Attempting this task with INDEX will drive you insane. The whole thing becomes clear and easy if the data is treated as a matrix.

Copy my D7 to D7:E16. You can expand the whole thing to 25 Tools and as many Variable Indexes as you care to by adding columns between the Tools and adding Variable Index 3 onwards. Make sure to deal carefully with the absolute and various cell references (where I hope I got it right!).

	A	B	C	D	E	F	G	H	I	J
Variable Index 1	Variable Index 2
Tool 1	Tool 1
Tool 2	Tool 3
Tool 4	Tool 5
Respondent ID	Current Role	Client	Variable Index 1	Variable Index 2	Tool 1	Tool 2	Tool 3	Tool 4	Tool 5
6390901863	Manager	Client 1
6393811019	Director	Client 1
6399578106	Analyst	Client 1
6401731956	Manager	Client 1
6400323399	Sr. Analyst	Client 2
6406122762	Sr. Analyst	Client 2
6406201649	Manager	Client 2
6390869805	Analyst	Client 2
6406150883	Director	Client 2
6408579156	Director	Client 2
Formula construction testing	formula	pointing
cell d7	Sum Respondent Tool Score
Sum Current Role Tool Score
Sum Client Tool Score
Sum Total Tool Score
single cell

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]96.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]88.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]88.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]90.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]64.29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]54.55[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]120.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]133.33[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]60.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]66.67[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]51.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]45.45[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]108.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]62.61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]64.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]46.96[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]48.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]63[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]96.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]96.43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet34​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E21[/TH]
[TD="align: left"]=(F7+G7+I7)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E22[/TH]
[TD="align: left"]=(F7+G7+I7+F10+G10+I10+F13+G13+I13)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E23[/TH]
[TD="align: left"]=(F7+F8+F9+F10+G7+G8+G9+G10+I7+I8+I9+I10)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E24[/TH]
[TD="align: left"]=(F17+G17+I17)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E25[/TH]
[TD="align: left"]=((F7+G7+I7)/(F7+G7+I7+F10+G10+I10+F13+G13+I13))/((F7+F8+F9+F10+G7+G8+G9+G10+I7+I8+I9+I10)/(F17+G17+I17))*100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]{=((SUM(MMULT($F$7:$J$16*($A$7:$A$16=$A7),TRANSPOSE(--($F$6:$J$6=A$2:A$4)))))/(SUM(MMULT($F$7:$J$16*($B$7:$B$16=$B7),TRANSPOSE(--($F$6:$J$6=A$2:A$4))))))/((SUM(MMULT($F$7:$J$16*($C$7:$C$16=$C7),TRANSPOSE(--($F$6:$J$6=A$2:A$4)))))/SUM(MMULT($F$7:$J$16,TRANSPOSE(--($F$6:$J$6=A$2:A$4)))))*100}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D21[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16*(A7:A16=A7),TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D22[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16*(B7:B16=B7),TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D23[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16*(C7:C16=C7),TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D24[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16,TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D25[/TH]
[TD="align: left"]{=((SUM(MMULT(F7:J16*(A7:A16=A7),TRANSPOSE(--(F6:J6=A2:A4)))))/(SUM(MMULT(F7:J16*(B7:B16=B7),TRANSPOSE(--(F6:J6=A2:A4))))))/((SUM(MMULT(F7:J16*(C7:C16=C7),TRANSPOSE(--(F6:J6=A2:A4)))))/SUM(MMULT(F7:J16,TRANSPOSE(--(F6:J6=A2:A4)))))*100}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]