Combining SUMPRODUCT with text/value conversion for

[Bruno37]

Hey!

I need a formula that works to get the SUM of the product (F7:K7) by corresponding array (F$6:K$6).
The key problem seems to be that F7:K7 is text. Thus, I'm trying to incorporate what each text equals {100,66,33,0} within the formula, but get a #VALUE! error:

=SUMPRODUCT(COUNTIF(F7:K7,{"High","Med","Low","Zero"})*{100,66,33,0})* F$6:K$6

Appreciate any help to make it work. Thanks a lot!

 

[Haui]

Put in any free space a lookup table, I chose N1:N4 = High Med Low Zero and O1:O4 = 100 66 33 0

And then in L7 and drag down: =SUM(INDEX(O$1:O$4,MATCH(F7:K7,N$1:N$4,))*F$6:K$6)
You may need to array-enter it.

See if that is what you had in mind.

Regards

 

[Eric W]

Welcome to the forum!

Try:

Book1
	F	G	H	I	J	K	L
6	10%	4%	4%	4%	4%	4%
7	High	Med	Low	Zero	Med	High	20.6
8	Med	Zero	Zero	Low	High	Med	14.56
Sheet2

Cell Formulas
Range	Formula
L7:L8	L7	=SUMPRODUCT($F$6:$K$6,MMULT({100,66,33,0},--(F7:K7={"High";"Med";"Low";"Zero"})))

 

[kweaver]

Should it be:

Book6
	F	G	H	I	J	K	L
6	10%	4%	4%	4%	4%	4%
7	High	High	High	Med	High	High	52.64
8	Med	Med	Med	Med	Med	Med	15.84
9	Zero	Zero	Zero	Zero	Zero	Zero	0
10	Med	Med	Med	Med	Med	Med	15.84
11	Low	Low	Low	Low	Low	Low	7.92
Sheet2

Cell Formulas
Range	Formula
L7:L11	L7	=SUM(IFERROR(COUNTIF(F7:K7,{"High","Med","Low","Zero"})*{100,66,33,0}*F$6:K$6,0))

Probably Eric is right with a matrix multiply to take each column separately with their percent values.

 

[KRice]

Nice solution, Eric!

 

[Eric W]

Nice solution, Eric!

Thanks! I get a kick out of MMULT formulas, and this one was just a little different than most.

 

[Bruno37]

Hey!

I need a formula that works to get the SUM of the product (F7:K7) by corresponding array (F$6:K$6).
The key problem seems to be that F7:K7 is text. Thus, I'm trying to incorporate what each text equals {100,66,33,0} within the formula, but get a #VALUE! error:

=SUMPRODUCT(COUNTIF(F7:K7,{"High","Med","Low","Zero"})*{100,66,33,0})* F$6:K$6

Appreciate any help to make it work. Thanks a lot!

Than

Welcome to the forum!

Try:

Book1
	F	G	H	I	J	K	L
6	10%	4%	4%	4%	4%	4%
7	High	Med	Low	Zero	Med	High	20.6
8	Med	Zero	Zero	Low	High	Med	14.56
Sheet2

Cell Formulas
Range	Formula
L7:L8	L7	=SUMPRODUCT($F$6:$K$6,MMULT({100,66,33,0},--(F7:K7={"High";"Med";"Low";"Zero"})))

THANK YOU SO MUCH ERIC!! ??? That worked smoothly perfect

 

[Bruno37]

Thanks! I get a kick out of MMULT formulas, and this one was just a little different than most.

You're very smart man! Thanks again

 

[Amit Tandon]

Try below, using SUMIF/SUMPRODUCT:

=SUMPRODUCT(SUMIF(F7:K7,{"High","Med","Low","Zero"},F$6:K$6)*({100,66,33,0}))