weighted sum

[blue333]

Hi

I need some help calculating a weighted sum. I have an example which shows how it should be done. Any help is great appreciated! Thanks in advance

Sample Data:
[TABLE="class: grid, width: 588"]
<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]Total[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] [/TD]
[TD]48%[/TD]
[TD]5%[/TD]
[TD]33%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]14%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]16%[/TD]
[TD]2%[/TD]
[TD]5%[/TD]
[TD]9%[/TD]
[TD]21%[/TD]
[TD]23%[/TD]
[TD]23%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]13%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]35%[/TD]
[TD]30%[/TD]
[TD] [/TD]
[TD]22%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]26%[/TD]
[TD] [/TD]
[TD]39%[/TD]
[TD] [/TD]
[TD]17%[/TD]
[TD]17%[/TD]
[TD] [/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]32%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4%[/TD]
[TD] [/TD]
[TD]36%[/TD]
[TD]28%[/TD]
[TD]100%[/TD]
[/TR]
</tbody><colgroup><col span="6"><col><col span="2"></colgroup>[/TABLE]


[TABLE="class: grid, width: 460"]
<tbody>[TR]
[TD] [/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]83[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]72[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]59[/TD]
[/TR]
</tbody><colgroup><col span="6"><col></colgroup>[/TABLE]

Expected Outcome:
[TABLE="class: grid, width: 466"]
<tbody>[TR]
[TD] [/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]68.29[/TD]
[TD="align: right"]45.86[/TD]
[TD="align: right"]59.52[/TD]
[TD="align: right"]35.10[/TD]
[TD="align: right"]32.14[/TD]
[TD="align: right"]50.10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]44.98[/TD]
[TD="align: right"]63.93[/TD]
[TD="align: right"]47.63[/TD]
[TD="align: right"]57.77[/TD]
[TD="align: right"]40.81[/TD]
[TD="align: right"]62.47[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]50.87[/TD]
[TD="align: right"]64.57[/TD]
[TD="align: right"]32.30[/TD]
[TD="align: right"]43.39[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]64.00[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]35.48[/TD]
[TD="align: right"]31.70[/TD]
[TD="align: right"]49.91[/TD]
[TD="align: right"]67.65[/TD]
[TD="align: right"]42.74[/TD]
[TD="align: right"]57.22[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]44.12[/TD]
[TD="align: right"]66.36[/TD]
[TD="align: right"]47.44[/TD]
[TD="align: right"]59.76[/TD]
[TD="align: right"]45.04[/TD]
[TD="align: right"]65.28[/TD]
[/TR]
</tbody><colgroup><col><col><col span="4"><col></colgroup>[/TABLE]

Sample Calculation:

68.29 = 0.48*95 + 0.05*14 + 0.33*47 + 0.14*47

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
A	B	C	D	E	F	G	Total	April	May	June	July	August	September	April	May	June	July	August	September
B	A	B
C	B	C
F	13%	C		F
H	D	H	35.11
Z	E	Z
F	87
G
	H

<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: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"][/TD]

[TD="align: right"]68.39[/TD]
[TD="align: right"]45.44[/TD]
[TD="align: right"]59.81[/TD]
[TD="align: right"]35.24[/TD]
[TD="align: right"]32.04[/TD]
[TD="align: right"]50.02[/TD]

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

[TD="align: right"]16%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]95[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44.25[/TD]
[TD="align: right"]63.19[/TD]
[TD="align: right"]47.18[/TD]
[TD="align: right"]57.43[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]61.69[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]50.84[/TD]
[TD="align: right"]64.7[/TD]
[TD="align: right"]32.31[/TD]
[TD="align: right"]43.26[/TD]
[TD="align: right"]49.91[/TD]
[TD="align: right"]63.81[/TD]

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

[TD="align: right"]26%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]

[TD="align: right"]31.11[/TD]
[TD="align: right"]49.38[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]42.38[/TD]
[TD="align: right"]56.45[/TD]

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

[TD="align: right"]32%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]52[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44.12[/TD]
[TD="align: right"]66.36[/TD]
[TD="align: right"]47.44[/TD]
[TD="align: right"]59.76[/TD]
[TD="align: right"]45.04[/TD]
[TD="align: right"]65.28[/TD]

[TD="align: center"]7[/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: right"]23[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]94[/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"]8[/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: right"]47[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/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"]9[/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"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]72[/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"]10[/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"]Z[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]59[/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"]11[/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: 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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]T2[/TH]
[TD="align: left"]=MMULT(INDEX($B$2:$H$6,MATCH($S2,$A$2:$A$6,0),0)+0,OFFSET($L$2:$L$8,0,MATCH(T$1,$L$1:$Q$1,0)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The numbers don't all match exactly, but they all seem to be in the ballpark. If you find one that's not right, let me know how it should be calculated.

 

[joeu2004]

Sample Calculation:
68.29 = 0.48*95 + 0.05*14 + 0.33*47 + 0.14*47

Your sample calculation is incorrect. It should be:

0.48*95 = 45.60
0.05*14 =  0.70
0.33*47 = 15.51
0.14*47 =  6.58
          -----
          68.39, not 68.29

The difference might be (a) your calculation error, or (b) due to rounding of either the percentages or (unlikely) the integers.

In any case, that might explain the differences in the results that I get below, which are the same as EricW's results.

Sample Data:

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD]48%
[/TD]
[TD]5%
[/TD]
[TD]33%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]14%
[/TD]
[TD]100%
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[TD]16%
[/TD]
[TD]2%
[/TD]
[TD]5%
[/TD]
[TD]9%
[/TD]
[TD]21%
[/TD]
[TD]23%
[/TD]
[TD]23%
[/TD]
[TD]100%
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]F
[/TD]
[TD]13%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]35%
[/TD]
[TD]30%
[/TD]
[TD][/TD]
[TD]22%
[/TD]
[TD]100%
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]H
[/TD]
[TD]26%
[/TD]
[TD][/TD]
[TD]39%
[/TD]
[TD][/TD]
[TD]17%
[/TD]
[TD]17%
[/TD]
[TD][/TD]
[TD]100%
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Z
[/TD]
[TD]32%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4%
[/TD]
[TD][/TD]
[TD]36%
[/TD]
[TD]28%
[/TD]
[TD]100%
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 400"]
<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]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]April
[/TD]
[TD]May
[/TD]
[TD]June
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[TD]September
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]A
[/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]98
[/TD]
[TD="align: right"]83
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]B
[/TD]
[TD="align: right"]95
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]90
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]46
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]C
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]68
[/TD]
[TD="align: right"]76
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]14
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]D
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"]71
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]51
[/TD]
[TD="align: right"]80
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]E
[/TD]
[TD="align: right"]52
[/TD]
[TD="align: right"]57
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"]49
[/TD]
[TD="align: right"]79
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]F
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]87
[/TD]
[TD="align: right"]70
[/TD]
[TD="align: right"]63
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]94
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]G
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"]91
[/TD]
[TD="align: right"]59
[/TD]
[TD="align: right"]63
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]H
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]70
[/TD]
[TD="align: right"]72
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]72
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]Z
[/TD]
[TD="align: right"]94
[/TD]
[TD="align: right"]73
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]76
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]59
[/TD]
[/TR]
</tbody>[/TABLE]

Expected Outcome:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: center"]A
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]B
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]C
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]D
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]E
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]F
[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]G
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]19
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]April
[/TD]
[TD="width: 64, bgcolor: transparent"]May
[/TD]
[TD="width: 64, bgcolor: transparent"]June
[/TD]
[TD="width: 64, bgcolor: transparent"]July
[/TD]
[TD="width: 64, bgcolor: transparent"]August
[/TD]
[TD="width: 64, bgcolor: transparent"]September
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20
[/TD]
[TD="width: 64, bgcolor: transparent"]B
[/TD]
[TD="width: 64, bgcolor: transparent"]

68.39​

[/TD]
[TD="width: 64, bgcolor: transparent"]

45.44​

[/TD]
[TD="width: 64, bgcolor: transparent"]

59.81​

[/TD]
[TD="width: 64, bgcolor: transparent"]

35.24​

[/TD]
[TD="width: 64, bgcolor: transparent"]

32.04​

[/TD]
[TD="width: 64, bgcolor: transparent"]

50.02​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]21
[/TD]
[TD="width: 64, bgcolor: transparent"]C
[/TD]
[TD="width: 64, bgcolor: transparent"]

44.25​

[/TD]
[TD="width: 64, bgcolor: transparent"]

63.19​

[/TD]
[TD="width: 64, bgcolor: transparent"]

47.18​

[/TD]
[TD="width: 64, bgcolor: transparent"]

57.43​

[/TD]
[TD="width: 64, bgcolor: transparent"]

40.30​

[/TD]
[TD="width: 64, bgcolor: transparent"]

61.69​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]22
[/TD]
[TD="width: 64, bgcolor: transparent"]F
[/TD]
[TD="width: 64, bgcolor: transparent"]

50.84​

[/TD]
[TD="width: 64, bgcolor: transparent"]

64.70​

[/TD]
[TD="width: 64, bgcolor: transparent"]

32.31​

[/TD]
[TD="width: 64, bgcolor: transparent"]

43.26​

[/TD]
[TD="width: 64, bgcolor: transparent"]

49.91​

[/TD]
[TD="width: 64, bgcolor: transparent"]

63.81​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]23
[/TD]
[TD="width: 64, bgcolor: transparent"]H
[/TD]
[TD="width: 64, bgcolor: transparent"]

35.11​

[/TD]
[TD="width: 64, bgcolor: transparent"]

31.11​

[/TD]
[TD="width: 64, bgcolor: transparent"]

49.38​

[/TD]
[TD="width: 64, bgcolor: transparent"]

67.00​

[/TD]
[TD="width: 64, bgcolor: transparent"]

42.38​

[/TD]
[TD="width: 64, bgcolor: transparent"]

56.45​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]24
[/TD]
[TD="width: 64, bgcolor: transparent"]Z
[/TD]
[TD="width: 64, bgcolor: transparent"]

44.12​

[/TD]
[TD="width: 64, bgcolor: transparent"]

66.36​

[/TD]
[TD="width: 64, bgcolor: transparent"]

47.44​

[/TD]
[TD="width: 64, bgcolor: transparent"]

59.76​

[/TD]
[TD="width: 64, bgcolor: transparent"]

45.04​

[/TD]
[TD="width: 64, bgcolor: transparent"]

65.28​

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


Formula:

B20: { =SUMPRODUCT(TRANSPOSE($B2:$H2), B$9:B$15) }

Copy B20 into B20:G24

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

 

[Eric W]

joeu2004 is correct with his math. And looking at his formula, it occurs to me that I can simplify mine. I originally assumed that the headings on the side would not necessarily be in order, so I used MATCH to locate the proper row. But if they are always in order, you can simplify my formula to:

T2: =MMULT($B2:$H2+0,L$2:L$8+0)

Same results as post 2.

 

[blue333]

Thank you both for your replies.

1. The sample calculation is "correct". I copied it from Excel but it had full decimal stored but only displayed the part without the decimal. When I copied it over to the forum it lost the decimals. I forgot to redo the expected results when the sample calculation was done. You guys are too sharp and noticed the mistake. Well done!

2. I got Eric's solution to work. I was going to try joeu2004 solution too until i realized something. This is perhaps due to the sample data I provided. No assumption should be made on the order of the letters in the columns or rows I have provided. It is a pure coincidence that they are "ABCDEFG". In practice, the letters are not guaranteed to be in any order. There could also be omissions (ie, ABDGJL).

Given this, how can the formulas be updated to actually search the letters? I have included revised examples with expected data. I cannot attach any files so I will expand the decimals to help get the expected results. If the results you get are close then that should be good enough.

Sample data:

[TABLE="class: grid, width: 632"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Z[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]47.61905%[/TD]
[TD]4.76190%[/TD]
[TD]33.33333%[/TD]
[TD][/TD]
[TD][/TD]
[TD]14.28571%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]16.27907%[/TD]
[TD]2.32558%[/TD]
[TD]4.65116%[/TD]
[TD]9.30233%[/TD]
[TD]20.93023%[/TD]
[TD]23.25581%[/TD]
[TD]23.25581%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]13.04348%[/TD]
[TD][/TD]
[TD][/TD]
[TD]34.78261%[/TD]
[TD]30.43478%[/TD]
[TD][/TD]
[TD]21.73913%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]26.08696%[/TD]
[TD][/TD]
[TD]39.13043%[/TD]
[TD][/TD]
[TD]17.39130%[/TD]
[TD]17.39130%[/TD]
[TD][/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]32.00000%[/TD]
[TD][/TD]
[TD][/TD]
[TD]4.00000%[/TD]
[TD][/TD]
[TD]36.00000%[/TD]
[TD]28.00000%[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 496"]
<tbody>[TR]
[TD][/TD]
[TD]2018-04[/TD]
[TD]2018-05[/TD]
[TD]2018-06[/TD]
[TD]2018-07[/TD]
[TD]2018-08[/TD]
[TD]2018-09[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]83[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]72[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]59[/TD]
[/TR]
</tbody>[/TABLE]

expected outcome
[TABLE="class: grid, width: 456"]
<tbody>[TR]
[TD][/TD]
[TD]2018-04[/TD]
[TD]2018-05[/TD]
[TD]2018-06[/TD]
[TD]2018-07[/TD]
[TD]2018-08[/TD]
[TD]2018-09[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]68.29[/TD]
[TD="align: right"]45.86[/TD]
[TD="align: right"]59.52[/TD]
[TD="align: right"]35.10[/TD]
[TD="align: right"]32.14[/TD]
[TD="align: right"]50.10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]53.77[/TD]
[TD="align: right"]67.28[/TD]
[TD="align: right"]43.44[/TD]
[TD="align: right"]60.07[/TD]
[TD="align: right"]37.47[/TD]
[TD="align: right"]58.28[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]63.65[/TD]
[TD="align: right"]69.43[/TD]
[TD="align: right"]26.22[/TD]
[TD="align: right"]46.74[/TD]
[TD="align: right"]45.13[/TD]
[TD="align: right"]57.91[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]42.78[/TD]
[TD="align: right"]34.48[/TD]
[TD="align: right"]46.43[/TD]
[TD="align: right"]69.57[/TD]
[TD="align: right"]39.96[/TD]
[TD="align: right"]53.74[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]44.12[/TD]
[TD="align: right"]66.36[/TD]
[TD="align: right"]47.44[/TD]
[TD="align: right"]59.76[/TD]
[TD="align: right"]45.04[/TD]
[TD="align: right"]65.28[/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much!!

 

[Eric W]

In that case, try:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
A	B	C	D	Z	F	G	Total	2018-04	2018-05	2018-06	2018-07	2018-08	2018-09	2018-04	2018-05	2018-06	2018-07	2018-08	2018-09
B	A	B
C	B	C
F	C	F
H	D	H
Z	E	Z
F
G
H
Z

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]47.62%[/TD]
[TD="align: right"]4.76%[/TD]
[TD="align: right"]33.33%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14.29%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"][/TD]

[TD="align: right"]68.285712[/TD]
[TD="align: right"]45.85714[/TD]
[TD="align: right"]59.52381[/TD]
[TD="align: right"]35.09523[/TD]
[TD="align: right"]32.14285[/TD]
[TD="align: right"]50.09524[/TD]

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

[TD="align: right"]16.28%[/TD]
[TD="align: right"]2.33%[/TD]
[TD="align: right"]4.65%[/TD]
[TD="align: right"]9.30%[/TD]
[TD="align: right"]20.93%[/TD]
[TD="align: right"]23.26%[/TD]
[TD="align: right"]23.26%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]95[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"][/TD]

[TD="align: right"]53.767437[/TD]
[TD="align: right"]67.27906[/TD]
[TD="align: right"]43.44185[/TD]
[TD="align: right"]60.06976[/TD]
[TD="align: right"]37.46512[/TD]
[TD="align: right"]58.27907[/TD]

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

[TD="align: right"]13.04%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]34.78%[/TD]
[TD="align: right"]30.43%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]21.74%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]63.652173[/TD]
[TD="align: right"]69.43478[/TD]
[TD="align: right"]26.21739[/TD]
[TD="align: right"]46.73913[/TD]
[TD="align: right"]45.13044[/TD]
[TD="align: right"]57.91304[/TD]

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

[TD="align: right"]26.09%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]39.13%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17.39%[/TD]
[TD="align: right"]17.39%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42.782605[/TD]
[TD="align: right"]34.47825[/TD]
[TD="align: right"]46.43478[/TD]
[TD="align: right"]69.56521[/TD]
[TD="align: right"]39.95652[/TD]
[TD="align: right"]53.73913[/TD]

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

[TD="align: right"]32.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36.00%[/TD]
[TD="align: right"]28.00%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]52[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44.12[/TD]
[TD="align: right"]66.36[/TD]
[TD="align: right"]47.44[/TD]
[TD="align: right"]59.76[/TD]
[TD="align: right"]45.04[/TD]
[TD="align: right"]65.28[/TD]

[TD="align: center"]7[/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: right"]23[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]94[/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"]8[/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: right"]47[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6[/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"]9[/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: right"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]72[/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"]10[/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: right"]94[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]59[/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]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T2[/TH]
[TD="align: left"]=SUMPRODUCT(INDEX($B$2:$H$6,MATCH($S2,$A$2:$A$6,0),0),SUMIF($K$2:$K$10,$B$1:$H$1,L$2:L$10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This assumes that the columns in tables 2 and 3 are in the same positions. If not, this version looks up the right column from table 2:

=SUMPRODUCT(INDEX($B$2:$H$6,MATCH($S2,$A$2:$A$6,0),0),SUMIF($K$2:$K$10,$B$1:$H$1,INDEX($L$2:$Q$10,0,MATCH(T$1,$L$1:$Q$1,0))))

 

[blue333]

wow!
you are a genius!

it works beautifully

thank you so much!!!!!!!!!

 

[Eric W]

You're very welcome!