SumIfs type of formula needed

[gheyman]

Type	Part	Quantity	Unit Price
OnHand	12571078	10.2	318.86
OnHand	12571079	8.8	5.09
OnHand	12571078	11.8	315.25
ScheduledReceipt	12571078	2.3	325.85
ScheduledReceipt	12571079	1.5	6.89
ScheduledReceipt	12571079	2.8	6.82

Here is the table of how the data typically comes.

I need a formula that will calculate the total quantity by Part/Type and I need to have the average unit price

I know I can add a column and calculate extended price and then use that to calculate an average but I really was looking to see if there was a way without doing that extra step

Example for OnHand/12571078
10.2 X 318.86 = 3252.33
11.8 X 315.25 = 3719.95

Sum of Total Pricer 6972.32 / sum of quantity 22 = average unit price of $316.92


Thanks

 

[awoohaw]

Does this work for you?

mr excel questions 15.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Type	Part	Quantity	Unit Price			Type	Part	Sum	Avg Price
2	OnHand	12571078	10.2	318.86			OnHand	12571078	6972.32	316.9236
3	OnHand	12571079	8.8	5.09			OnHand	12571079	44.79	5.089773
4	OnHand	12571078	11.8	315.25			ScheduledReceipt	12571078	749.46	325.8522
5	ScheduledReceipt	12571078	2.3	325.85			ScheduledReceipt	12571079	29.44	6.846512
6	ScheduledReceipt	12571079	1.5	6.89
7	ScheduledReceipt	12571079	2.8	6.82
8
Sheet19

Cell Formulas
Range	Formula
I2:I5	I2	=SUM((--($A$2:$A$7=G2))*(--(H2=$B$2:$B$7))*ROUND($C$2:$C$7*$D$2:$D$7,2))
J2:J5	J2	=SUM((--($A$2:$A$7=G2))*(--(H2=$B$2:$B$7))*ROUND($C$2:$C$7*$D$2:$D$7,2))/SUM((--($A$2:$A$7=G2))*(--($B$2:$B$7=H2))*$C$2:$C$7)