find variable, auto sum column

[jan21]

A	B	C	D	E	F	Multiplier
Z	10			10		1
Y		50	50		10	2
X	4			4	10	3
W					10	4

<tbody>
</tbody>

Enter #

SUM

<tbody>
</tbody>

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!!