Condensed-do-all SUMPRODUCT?

[fun2excel4money]

Hello Community,

Long time lurker, first time poster. I am attempting to make a more concise formula. The situation is as follows:

	JOHN	JIM	JESS			MOD-1	MOD-2	MOD-3	MOD-4	MOD-5
ORD	.5	.3	.2			ORD	ORD	AVG	ORD	AVG
AVG	.2	.3	.5			10000	20000	30000	40000	50000
CA	35000	30000	35000		CA	10000	0	0	40000	50000
NY	10000	6000	4000		NY	0	20000	0	0	0
VT	26000	21000	23000		VT	0	0	30000	40000	0

<tbody>
</tbody>

In this scenario, I am focusing on B5 where the output is 35000. I am attempting to find A5 in table F5:F7. Once I find it, I want it to sum up the amounts in G5:G7 using a SUMPRODUCT. The extra bone that I want to throw in is that I then want to specifically sum up all items that are ORD and all that are AVG in G2:K2. I want the numbers to then multiply by the person's ORD percentage in B2:D3.

To illustrate with numbers, CA has a total ORD modification of 50000 and a total AVG modification of 50000. As it relates to John, his ORD % is 50% and his AVG % is 20%. His share of ORD and AVG modifications would be 50000 * .5 + 50000 * .2, or 35000.

The formula I am currently using for B5 is:

=SUMPRODUCT(($F$5:$F$7=$A5)*($G$2:$K$2=$G$2)*$G$5:$K$7)*SUMPRODUCT(($A$2:$A$3=$G$2)*B$2:B$3)+SUMPRODUCT(($F$5:$F$7=$A5)*($G$2:$K$2=$I$2)*$G$5:$K$7)*SUMPRODUCT(($A$2:$A$3=$I$2)*B$2:B$3)

I am looking for a formula that might allow me to use one SUMPRODUCT as opposed to the sum of two SUMPRODUCTs. The actual situation I have, I am using five additional SUMPRODUCT, hence my curiosity of the situation. The problem I am having is that each person has his own percentages and is referenced directly above where I want to enter the formula. G2:K2 are used to let me know which modification is subject to which allocation, but not the allocation percentages attributable to the person.

Please let me know if there is anything I can clarify. A virtual eggnog to those who can help! Thank you!

 

[Aladin Akyurek]

In B5 enter, copy across to D5, and down:

=SUMPRODUCT(INDEX($G$5:$K$7,MATCH($A5,$F$5:$F$7,0),0),SUMIFS(B$2:B$3,$A$2:$A$3,$G$2:$K$2))

 

[fun2excel4money]

ABSOLUTELY INCREDIBLE! Thank you, thank you!

 

[Aladin Akyurek]

ABSOLUTELY INCREDIBLE! Thank you, thank you!

You are welcome.