SUMPRODUCT Help

[Banker1]

I was helping another member with SUMPRODUCT, he was trying to calc weighted averages given a specific data set with a few different variables (see the HTML below).

Sumproduct_Solution.xls
	A	B	C	D	E	F	G	H	I
1	Names	% Code	Earnings	Percent	Combined
2	Mike	A	3,300	100%	375
3	Dave	B	12,600	100%	1,650
4	Bill	C	3,900	100%	650
5	Carl	D	3,500	90%	405
6
7
8	Mike	Dave	Bill	Carl		A	B	C	D
9	100	200	300	400		25%	0%	0%	10%
10	500	600	700	800		10%	20%	0%	10%
11	900	1,000	800	600		10%	20%	30%	10%
12	600	7,300	200	100		5%	10%	20%	10%
13	500	300	200	100		25%	0%	0%	20%
14	200	2,000	300	100		10%	20%	0%	5%
15	200	800	900	800		10%	20%	30%	20%
16	300	400	500	600		5%	10%	20%	5%
17	3,300	12,600	3,900	3,500		100%	100%	100%	90%
18	-	-	-	-		0%	0%	0%	0%
Sheet1

The SUMPRODUCT formula (highlighted in yellow) works as long as you see it, if I change any of the variables (Names and % Code columns) the result goes to 0. For example, Dave with a D % code results in 0 and should be 1,250. In looking at the arrays SUMPRODUCT creates I noticed the 1's & 0's don't line up which I think is causing the issue, unfortunately for me I can't figure out how to get it to work...

Any ideas?

 

[RoryA]

I would use this in E2:

=SUMPRODUCT(INDEX($A$9:$D$16,,MATCH($A2,$A$8:$D$8,0))*INDEX($F$9:$I$16,,MATCH($B2,$F$8:$I$8,0)))

 

[Banker1]

Thank you sir, I was trying to get to that but forgot the ",," in the Match formula (doh!). Must have a case of the Mondays...