Sumproduct In Excel

[varunwalla]

Hello everyone,

I need to calculate the average price of a product let say Orange.

1. First 30 Qty of Orange is bought at 8.85 so the value becomes 265.50
2. Next 5 Qty of Orange is bought at 8.75 so the value becomes 43.75
3. Total cost of 35 Qty is 309.25
4. Now taking the average value of 35 is 8.84 ( rounding to 2 digit ) i.e 309.25 divide by 35

At the moment i am using this procedure to calculate the average value

=SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Orange")

is there any techniques to simplify this for each product i am manuly entering the reference cells for apple i am entering it as

=SUMPRODUCT(E2+E5)/(C2+C5)*(B5="Apple")

is there any formula to simplify these steps

Sumproduct.xlsx
	A	B	C	D	E	F	G	H	I
1	S.No	Product Name	Quantity	Price	Total Price	Average Price Cost		Total Investment Cost
2	1	Apple	5	198.00	990.00	Orange	8.84	Orange
3	2	Orange	30	8.85	265.50	Apple	204.00	Apple
4	3	Orange	5	8.75	43.75
5	4	Apple	5	210.00	1050.00
Sheet 1

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Orange")
G3	G3	=SUMPRODUCT(E2+E5)/(C2+C5)*(B5="Apple")
A3:A5	A3	=(A2+1)
E2:E5	E2	=(C2*D2)

Any help would be much appreciated

 

[RoryA]

It's SUMIF, not SUM in I2.

 

[varunwalla]

works perfectly man your awesome

 

[RoryA]

Glad we could help.

 

[varunwalla]

One more thing tired out is to try out If function i.e c3=sale then in h2 the calculation should be ignored too much if function googled out like if, countif, nested if what to try out i.e the value in h2 should be display as 8.84

Sumproduct.xlsx
	A	B	C	D	E	F	G	H	I	J
1	S.No	Product Name	Type	Quantity	Price	Total Price	Average Price Cost		Total Investment Cost
2	1	Apple	Purchase	5	198.00	990.00	Orange	9.17	Orange	641.75
3	2	Orange	Purchase	30	8.85	265.50	Apple	204	Apple	2040.00
4	3	Orange	Purchase	5	8.75	43.75
5	4	Apple	Purchase	5	210.00	1050.00
6	5	Orange	Sale	35	9.50	332.50
Sheet 1

Cell Formulas
Range	Formula
H2:H3	H2	=ROUND(SUMIF($B:$B,$G2,$F:$F)/SUMIF($B:$B,$G2,$D:$D),2)
J2:J3	J2	=SUMIF($B:$B,$I2,$F:$F)
A3:A6	A3	=(A2+1)
F2:F6	F2	=(D2*E2)

 

[RoryA]

You need SUMIFS for multiple criteria:

=ROUND(SUMIFS($F:$F,$B:$B,$G2,$C:$C,"<>Sale")/SUMIFS($D:$D,$B:$B,$G2,$C:$C,"<>Sale"),2)

Note that with SUMIFS, the range to sum goes first, not last.