find variable, auto sum column

[jan21]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]Multiplier
[/TD]
[/TR]
[TR]
[TD]Z
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD]50
[/TD]
[TD][/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]10
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]W
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Enter #
[/TD]
[TD]SUM
[/TD]
[/TR]
</tbody>[/TABLE]

When I write "B" in the field "Enter #", I want that it shows the sum of the named column, in this case sum=(10*1)+(4*3)=22. How can I do this?

 

[steve the fish]

Hi. Try this:

=SUMPRODUCT(INDEX($B$1:$F$4,,CODE($A$6)-65),$G$1:$G$4)

 

[jan21]

What is this "-65"?

It doesnt work yet.

 

[steve the fish]

If it doesnt work then you are not pointing to the correct cells. CODE(A6)-65 is being used to convert eg 'B' to a number. In this case 1 as its the first column of the INDEX.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	Multiplier
2	Z	10			10		1
3	Y		50	50		10	2
4	X	4			4	10	3
5	W					10	4
6
7	Enter #	B	22
Names

Cell Formulas
Range	Formula
C7	=SUMPRODUCT((B1:F1=B7)*(B2:F5)*(G2:G5))

 

[jan21]

@Fluff, that works!

 

[Fluff]

You're welcome & thanks for the feedback

 

[jan21]

Thank you!!