Sumproduct In Excel

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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

Links? Where??
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
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
My formula, not yours.
 

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Perfect the solutions works fantastically
 

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top