# Sumproduct In Excel

#### varunwalla

##### New Member
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

Code:
``=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

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

is there any formula to simplify these steps

Sumproduct.xlsx
ABCDEFGHI
1S.NoProduct NameQuantityPriceTotal PriceAverage Price CostTotal Investment Cost
21Apple5198.00990.00Orange8.84Orange
32Orange308.85265.50Apple204.00Apple
43Orange58.7543.75
54Apple5210.001050.00
Sheet 1
Cell Formulas
RangeFormula
G2G2=SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Orange")
G3G3=SUMPRODUCT(E2+E5)/(C2+C5)*(B5="Apple")
A3:A5A3=(A2+1)
E2:E5E2=(C2*D2)

Any help would be much appreciated

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### RoryA

##### MrExcel MVP, Moderator
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

##### New Member
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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### MrExcel MVP, Moderator
My formula, not yours.

#### varunwalla

##### New Member

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
ABCDEFGHI
1S.NoProduct NameQuantityPriceTotal PriceAverage Price CostTotal Investment Cost
21Apple5198.00990.00Orange8.84Orange
32Orange308.85265.50Apple8.84Apple
43Orange58.7543.75
54Apple5210.001050.00
Sheet 1
Cell Formulas
RangeFormula
G2G2=ROUND(SUMIF(\$B:\$B,\$F2,\$E:\$E)/SUMIF(\$B:\$B,\$F2,\$C:\$C),2)
G3G3=ROUND(SUMIF(\$B:\$B,\$F2,\$E:\$E)/SUMIF(\$B:\$B,\$F2,\$C:\$C),2)
A3:A5A3=(A2+1)
E2:E5E2=(C2*D2)

#### RoryA

##### MrExcel MVP, Moderator
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

##### New Member
Perfect the solutions works fantastically

#### varunwalla

##### New Member
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
ABCDEFGHI
1S.NoProduct NameQuantityPriceTotal PriceAverage Price CostTotal Investment Cost
21Apple5198.00990.00Orange8.84Orange2349.25
32Orange308.85265.50Apple204Apple
43Orange58.7543.75
54Apple5210.001050.00
Sheet 1
Cell Formulas
RangeFormula
I2I2=SUM(\$B:\$B,\$H2,\$E:\$E)
G2:G3G2=ROUND(SUMIF(\$B:\$B,\$F2,\$E:\$E)/SUMIF(\$B:\$B,\$F2,\$C:\$C),2)
A3:A5A3=(A2+1)
E2:E5E2=(C2*D2)

Replies
5
Views
220
Replies
3
Views
42
Replies
6
Views
246
Replies
4
Views
932
Replies
13
Views
316

1,129,940
Messages
5,639,099
Members
417,072
Latest member
JaimeDee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back