Weighted Average with multiple Criteria

[EdE]

Hi,
I have a challenge of creating a weighted average with multiple criteria. I have data that is similar to this.
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"]PN[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Dallas[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Dallas[/TD]
[TD="align: right"]5678[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]NY[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]LA[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]LA[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]NY[/TD]
[TD="align: right"]5678[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]England[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Ireland[/TD]
[TD="align: right"]5678[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Ireland[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Ireland[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Scotland[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Scotland[/TD]
[TD="align: right"]5678[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]

I need to find the weighted average of Sales based on quantity but where a certain combination of criteria is defined. I need the weighted Average of PN 1234 in the USA region in the LA location.
I know how to do this with one criteria but not multiple. Sumproduct formulas are not my strong suit.
thanks!!

 

[63falcondude]

Try this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Region	Location	PN	Qty	Sales		PN	Region	Location	Weighted Avg
2	USA	Dallas	1234	1	100		1234	USA	LA	9100
3	USA	Dallas	5678	100	10000
4	USA	NY	1234	100	10000
5	USA	LA	1234	10	100
6	USA	LA	1234	100	10000
7	USA	NY	5678	10	1000
8	UK	England	1234	2	200
9	UK	Ireland	5678	200	20000
10	UK	Ireland	1234	200	20000
11	UK	Ireland	1234	20	2000
12	UK	Scotland	1234	200	20000
13	UK	Scotland	5678	20	2000
Sheet1

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(--($A$2:$A$13=H2),--($B$2:$B$13=I2),--($C$2:$C$13=G2),$D$2:$D$13,$E$2:$E$13)/SUMIFS($D$2:$D$13,$A$2:$A$13,H2,$B$2:$B$13,I2,$C$2:$C$13,G2)