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]

Welcome to the forum.

It looks like you want this in G2:

=ROUND(SUMIF($B:$B,$F2,$E:$E)/SUMIF($B:$B,$F2,$C:$C),2)

 

[varunwalla]

Thanks for the reply but looking for multiple products i.e if the product is Apple then average value changes each time i can't specify b3=orange also note b4=orange so i am taking just one reference as b3 is it possible to make b:b=orange or b:b=apple to calculate them

 

[RoryA]

Did you actually try the formula? That is what it does - it looks for the value in F2 anywhere in column B and then adds up all the column E entries and divides by the total of the column C entries.

 

[varunwalla]

but when i use this in g2 =SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Apple") the value becomes zero compared to =SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Orange") because it can't find the name Apple in cell B3 so it becomes zero hope i am clear now

 

[RoryA]

My formula, not yours.

 

[varunwalla]

yes tired your solutions in F2 it works fine for Orange where in F3 it display the same value as in G2 but the value should be displayed as 204 instead of 8.84

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	8.84	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	=ROUND(SUMIF($B:$B,$F2,$E:$E)/SUMIF($B:$B,$F2,$C:$C),2)
G3	G3	=ROUND(SUMIF($B:$B,$F2,$E:$E)/SUMIF($B:$B,$F2,$C:$C),2)
A3:A5	A3	=(A2+1)
E2:E5	E2	=(C2*D2)

 

[RoryA]

If you copy/fill the formula down, instead of pasting the exact same formula into both cells, it will automatically adjust the formula to use F3 not F2 and the calculation will be correct.

 

[varunwalla]

Perfect the solutions works fantastically

 

[varunwalla]

tired the sum function with reference by the formula but the value is wrong could you guide what wrong i am doing here for orange the value should be 309.25 but showing sum of all i.e 2349.25

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	2349.25
3	2	Orange	30	8.85	265.50	Apple	204	Apple
4	3	Orange	5	8.75	43.75
5	4	Apple	5	210.00	1050.00
Sheet 1

Cell Formulas
Range	Formula
I2	I2	=SUM($B:$B,$H2,$E:$E)
G2:G3	G2	=ROUND(SUMIF($B:$B,$F2,$E:$E)/SUMIF($B:$B,$F2,$C:$C),2)
A3:A5	A3	=(A2+1)
E2:E5	E2	=(C2*D2)