Sumproduct and sumifs nested

[marius m]

Hello,

I apologize in advance if this has been asked and answered already, i have not found any suitable answer that resolves my problem

I will try to explain. I am trying to create a simple inventory valuation template which multiplies price by quantity for items line by line, accumulates the result of this and then calculates the average price. The problem is I have thousands of lines of items with their changing prices.
How can i create a formula that returns sumproduct result based on a selected material. basically a sumproduct but for select materials.
[TABLE="width: 1446"]
<tbody>[TR]
[TD]Material ID[/TD]
[TD] Quantity[/TD]
[TD] Value[/TD]
[TD] Price[/TD]
[TD] Cummulative volume[/TD]
[TD] Ending Value Cumm[/TD]
[TD] Ending Price[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,480.00[/TD]
[TD] 116,104.00[/TD]
[TD] 2.30[/TD]
[TD] 50,480[/TD]
[TD] 116,104.00[/TD]
[TD] 2.30[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,240.00[/TD]
[TD] 115,552.00[/TD]
[TD] 2.30[/TD]
[TD] 100,720[/TD]
[TD] 231,656.00[/TD]
[TD] 2.30[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,040.00[/TD]
[TD] 115,092.00[/TD]
[TD] 2.30[/TD]
[TD] 150,760[/TD]
[TD] 346,748.00[/TD]
[TD] 2.30[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,260.00[/TD]
[TD] 115,598.00[/TD]
[TD] 2.30[/TD]
[TD] 201,020[/TD]
[TD] 462,346.00[/TD]
[TD] 2.30[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,620.00[/TD]
[TD] 116,426.00[/TD]
[TD] 2.30[/TD]
[TD] 251,640[/TD]
[TD] 578,772.00[/TD]
[TD] 2.30[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,160.00[/TD]
[TD] 103,868.00[/TD]
[TD] 2.30[/TD]
[TD] 296,800[/TD]
[TD] 682,640.00[/TD]
[TD] 2.30[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 17,700.00[/TD]
[TD] 34,692.00[/TD]
[TD] 1.96[/TD]
[TD] 314,500[/TD]
[TD] 717,332.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 36,700.00[/TD]
[TD] 80,740.00[/TD]
[TD] 2.20[/TD]
[TD] 351,200[/TD]
[TD] 798,072.00[/TD]
[TD] 2.27[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,080.00[/TD]
[TD] 115,184.00[/TD]
[TD] 2.30[/TD]
[TD] 401,280[/TD]
[TD] 913,256.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,200.00[/TD]
[TD] 115,460.00[/TD]
[TD] 2.30[/TD]
[TD] 451,480[/TD]
[TD] 1,028,716.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,100.00[/TD]
[TD] 115,230.00[/TD]
[TD] 2.30[/TD]
[TD] 501,580[/TD]
[TD] 1,143,946.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 47,040.00[/TD]
[TD] 108,192.00[/TD]
[TD] 2.30[/TD]
[TD] 548,620[/TD]
[TD] 1,252,138.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,920.00[/TD]
[TD] 114,816.00[/TD]
[TD] 2.30[/TD]
[TD] 598,540[/TD]
[TD] 1,366,954.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,140.00[/TD]
[TD] 103,822.00[/TD]
[TD] 2.30[/TD]
[TD] 643,680[/TD]
[TD] 1,470,776.00[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 46,120.00[/TD]
[TD] 106,076.00[/TD]
[TD] 2.30[/TD]
[TD] 689,800[/TD]
[TD] 1,576,852.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,920.00[/TD]
[TD] 114,816.00[/TD]
[TD] 2.30[/TD]
[TD] 739,720[/TD]
[TD] 1,691,668.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,040.00[/TD]
[TD] 115,092.00[/TD]
[TD] 2.30[/TD]
[TD] 789,760[/TD]
[TD] 1,806,760.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 47,200.00[/TD]
[TD] 108,560.00[/TD]
[TD] 2.30[/TD]
[TD] 836,960[/TD]
[TD] 1,915,320.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 47,640.00[/TD]
[TD] 109,572.00[/TD]
[TD] 2.30[/TD]
[TD] 884,600[/TD]
[TD] 2,024,892.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,120.00[/TD]
[TD] 115,276.00[/TD]
[TD] 2.30[/TD]
[TD] 934,720[/TD]
[TD] 2,140,168.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 39,560.00[/TD]
[TD] 90,988.00[/TD]
[TD] 2.30[/TD]
[TD] 974,280[/TD]
[TD] 2,231,156.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,220.00[/TD]
[TD] 115,506.00[/TD]
[TD] 2.30[/TD]
[TD] 1,024,500[/TD]
[TD] 2,346,662.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,920.00[/TD]
[TD] 114,816.00[/TD]
[TD] 2.30[/TD]
[TD] 1,074,420[/TD]
[TD] 2,461,478.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,420.00[/TD]
[TD] 115,966.00[/TD]
[TD] 2.30[/TD]
[TD] 1,124,840[/TD]
[TD] 2,577,444.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,320.00[/TD]
[TD] 104,236.00[/TD]
[TD] 2.30[/TD]
[TD] 1,170,160[/TD]
[TD] 2,681,680.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 44,900.00[/TD]
[TD] 103,270.00[/TD]
[TD] 2.30[/TD]
[TD] 1,215,060[/TD]
[TD] 2,784,950.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,960.00[/TD]
[TD] 114,908.00[/TD]
[TD] 2.30[/TD]
[TD] 1,265,020[/TD]
[TD] 2,899,858.00[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 39,663.28[/TD]
[TD] 87,259.22[/TD]
[TD] 2.20[/TD]
[TD] 1,304,683.28[/TD]
[TD] 2,987,117.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 22,780.00[/TD]
[TD] 52,394.00[/TD]
[TD] 2.30[/TD]
[TD] 1,327,463[/TD]
[TD] 3,039,511.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,420.00[/TD]
[TD] 115,966.00[/TD]
[TD] 2.30[/TD]
[TD] 1,377,883.28[/TD]
[TD] 3,155,477.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,720.00[/TD]
[TD] 114,356.00[/TD]
[TD] 2.30[/TD]
[TD] 1,427,603[/TD]
[TD] 3,269,833.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,700.00[/TD]
[TD] 114,310.00[/TD]
[TD] 2.30[/TD]
[TD] 1,477,303.28[/TD]
[TD] 3,384,143.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,700.00[/TD]
[TD] 114,310.00[/TD]
[TD] 2.30[/TD]
[TD] 1,527,003[/TD]
[TD] 3,498,453.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 43,140.00[/TD]
[TD] 94,908.00[/TD]
[TD] 2.20[/TD]
[TD] 1,570,143.28[/TD]
[TD] 3,593,361.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 27,640.00[/TD]
[TD] 63,572.00[/TD]
[TD] 2.30[/TD]
[TD] 1,597,783[/TD]
[TD] 3,656,933.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,800.00[/TD]
[TD] 114,540.00[/TD]
[TD] 2.30[/TD]
[TD] 1,647,583.28[/TD]
[TD] 3,771,473.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 27,500.00[/TD]
[TD] 60,500.00[/TD]
[TD] 2.20[/TD]
[TD] 1,675,083[/TD]
[TD] 3,831,973.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 32,820.00[/TD]
[TD] 72,204.00[/TD]
[TD] 2.20[/TD]
[TD] 1,707,903.28[/TD]
[TD] 3,904,177.22[/TD]
[TD] 2.29[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 15,140.00[/TD]
[TD] 29,674.40[/TD]
[TD] 1.96[/TD]
[TD] 1,723,043[/TD]
[TD] 3,933,851.62[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 13,880.00[/TD]
[TD] 20,820.00[/TD]
[TD] 1.50[/TD]
[TD] 1,736,923.28[/TD]
[TD] 3,954,671.62[/TD]
[TD] 2.28[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 20,080.00[/TD]
[TD] 39,356.80[/TD]
[TD] 1.96[/TD]
[TD] 1,757,003[/TD]
[TD] 3,994,028.42[/TD]
[TD] 2.27[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,360.00[/TD]
[TD] 88,905.60[/TD]
[TD] 1.96[/TD]
[TD] 1,802,363.28[/TD]
[TD] 4,082,934.02[/TD]
[TD] 2.27[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 48,000.00[/TD]
[TD] 94,080.00[/TD]
[TD] 1.96[/TD]
[TD] 1,850,363[/TD]
[TD] 4,177,014.02[/TD]
[TD] 2.26[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 42,620.00[/TD]
[TD] 83,535.20[/TD]
[TD] 1.96[/TD]
[TD] 1,892,983.28[/TD]
[TD] 4,260,549.22[/TD]
[TD] 2.25[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 17,740.00[/TD]
[TD] 34,770.40[/TD]
[TD] 1.96[/TD]
[TD] 1,910,723[/TD]
[TD] 4,295,319.62[/TD]
[TD] 2.25[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,260.00[/TD]
[TD] 88,709.60[/TD]
[TD] 1.96[/TD]
[TD] 1,955,983.28[/TD]
[TD] 4,384,029.22[/TD]
[TD] 2.24[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 34,340.00[/TD]
[TD] 67,306.40[/TD]
[TD] 1.96[/TD]
[TD] 1,990,323[/TD]
[TD] 4,451,335.62[/TD]
[TD] 2.24[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 17,560.00[/TD]
[TD] 34,417.60[/TD]
[TD] 1.96[/TD]
[TD] 2,007,883.28[/TD]
[TD] 4,485,753.22[/TD]
[TD] 2.23[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,060.00[/TD]
[TD] 88,317.60[/TD]
[TD] 1.96[/TD]
[TD] 2,052,943[/TD]
[TD] 4,574,070.82[/TD]
[TD] 2.23[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 44,480.00[/TD]
[TD] 91,539.84[/TD]
[TD] 2.06[/TD]
[TD] 2,097,423[/TD]
[TD] 4,665,610.66[/TD]
[TD] 2.22[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 29,980.00[/TD]
[TD] 61,698.84[/TD]
[TD] 2.06[/TD]
[TD] 2,127,403[/TD]
[TD] 4,727,309.50[/TD]
[TD] 2.22[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 19,720.00[/TD]
[TD] 40,583.76[/TD]
[TD] 2.06[/TD]
[TD] 2,147,123[/TD]
[TD] 4,767,893.26[/TD]
[TD] 2.22[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 39,980.00[/TD]
[TD] 82,278.84[/TD]
[TD] 2.06[/TD]
[TD] 2,187,103[/TD]
[TD] 4,850,172.10[/TD]
[TD] 2.22[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 41,300.00[/TD]
[TD] 84,995.40[/TD]
[TD] 2.06[/TD]
[TD] 2,228,403[/TD]
[TD] 4,935,167.50[/TD]
[TD] 2.21[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 47,400.00[/TD]
[TD] 97,549.20[/TD]
[TD] 2.06[/TD]
[TD] 2,275,803[/TD]
[TD] 5,032,716.70[/TD]
[TD] 2.21[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 42,740.00[/TD]
[TD] 87,958.92[/TD]
[TD] 2.06[/TD]
[TD] 2,318,543[/TD]
[TD] 5,120,675.62[/TD]
[TD] 2.21[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 46,380.00[/TD]
[TD] 95,450.04[/TD]
[TD] 2.06[/TD]
[TD] 2,364,923[/TD]
[TD] 5,216,125.66[/TD]
[TD] 2.21[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,600.00[/TD]
[TD] 83,554.80[/TD]
[TD] 2.06[/TD]
[TD] 2,405,523[/TD]
[TD] 5,299,680.46[/TD]
[TD] 2.20[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 41,660.00[/TD]
[TD] 85,736.28[/TD]
[TD] 2.06[/TD]
[TD] 2,447,183[/TD]
[TD] 5,385,416.74[/TD]
[TD] 2.20[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (30,020.00)[/TD]
[TD] (61,781.16)[/TD]
[TD] 2.06[/TD]
[TD] 2,417,163[/TD]
[TD] 5,323,635.58[/TD]
[TD] 2.20[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,180.00[/TD]
[TD] 103,270.44[/TD]
[TD] 2.06[/TD]
[TD] 2,467,343[/TD]
[TD] 5,426,906.02[/TD]
[TD] 2.20[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,560.00[/TD]
[TD] 104,052.48[/TD]
[TD] 2.06[/TD]
[TD] 2,517,903[/TD]
[TD] 5,530,958.50[/TD]
[TD] 2.20[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,660.00[/TD]
[TD] 64,039.50[/TD]
[TD] 1.58[/TD]
[TD] 2,558,563[/TD]
[TD] 5,594,998.00[/TD]
[TD] 2.19[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 30,440.00[/TD]
[TD] 62,645.52[/TD]
[TD] 2.06[/TD]
[TD] 2,589,003[/TD]
[TD] 5,657,643.52[/TD]
[TD] 2.19[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 35,640.00[/TD]
[TD] 73,347.12[/TD]
[TD] 2.06[/TD]
[TD] 2,624,643[/TD]
[TD] 5,730,990.64[/TD]
[TD] 2.18[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 35,280.00[/TD]
[TD] 72,606.24[/TD]
[TD] 2.06[/TD]
[TD] 2,659,923[/TD]
[TD] 5,803,596.88[/TD]
[TD] 2.18[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 34,840.00[/TD]
[TD] 54,873.00[/TD]
[TD] 1.58[/TD]
[TD] 2,694,763[/TD]
[TD] 5,858,469.88[/TD]
[TD] 2.17[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 30,280.00[/TD]
[TD] 59,772.72[/TD]
[TD] 1.97[/TD]
[TD] 2,725,043[/TD]
[TD] 5,918,242.60[/TD]
[TD] 2.17[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 41,380.00[/TD]
[TD] 81,684.12[/TD]
[TD] 1.97[/TD]
[TD] 2,766,423[/TD]
[TD] 5,999,926.72[/TD]
[TD] 2.17[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 54,800.00[/TD]
[TD] 112,778.40[/TD]
[TD] 2.06[/TD]
[TD] 2,821,223[/TD]
[TD] 6,112,705.12[/TD]
[TD] 2.17[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,080.00[/TD]
[TD] 92,774.64[/TD]
[TD] 2.06[/TD]
[TD] 2,866,303[/TD]
[TD] 6,205,479.76[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 37,780.00[/TD]
[TD] 74,577.72[/TD]
[TD] 1.97[/TD]
[TD] 2,904,083[/TD]
[TD] 6,280,057.48[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (45,080.00)[/TD]
[TD] (92,774.64)[/TD]
[TD] 2.06[/TD]
[TD] 2,859,003[/TD]
[TD] 6,187,282.84[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (54,800.00)[/TD]
[TD] (112,778.40)[/TD]
[TD] 2.06[/TD]
[TD] 2,804,203[/TD]
[TD] 6,074,504.44[/TD]
[TD] 2.17[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 45,080.00[/TD]
[TD] 92,774.64[/TD]
[TD] 2.06[/TD]
[TD] 2,849,283[/TD]
[TD] 6,167,279.08[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 54,800.00[/TD]
[TD] 112,778.40[/TD]
[TD] 2.06[/TD]
[TD] 2,904,083[/TD]
[TD] 6,280,057.48[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,500.00[/TD]
[TD] 63,787.50[/TD]
[TD] 1.58[/TD]
[TD] 2,944,583[/TD]
[TD] 6,343,844.98[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 35,280.00[/TD]
[TD] 72,606.24[/TD]
[TD] 2.06[/TD]
[TD] 2,979,863[/TD]
[TD] 6,416,451.22[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 35,640.00[/TD]
[TD] 73,347.12[/TD]
[TD] 2.06[/TD]
[TD] 3,015,503[/TD]
[TD] 6,489,798.34[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (35,640.00)[/TD]
[TD] (73,347.12)[/TD]
[TD] 2.06[/TD]
[TD] 2,979,863[/TD]
[TD] 6,416,451.22[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (35,280.00)[/TD]
[TD] (72,606.24)[/TD]
[TD] 2.06[/TD]
[TD] 2,944,583[/TD]
[TD] 6,343,844.98[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (50,560.00)[/TD]
[TD] (104,052.48)[/TD]
[TD] 2.06[/TD]
[TD] 2,894,023[/TD]
[TD] 6,239,792.50[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,560.00[/TD]
[TD] 104,052.48[/TD]
[TD] 2.06[/TD]
[TD] 2,944,583[/TD]
[TD] 6,343,844.98[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,820.00[/TD]
[TD] 64,291.50[/TD]
[TD] 1.58[/TD]
[TD] 2,985,403[/TD]
[TD] 6,408,136.48[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 20,220.00[/TD]
[TD] 41,612.76[/TD]
[TD] 2.06[/TD]
[TD] 3,005,623[/TD]
[TD] 6,449,749.24[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,260.00[/TD]
[TD] 82,855.08[/TD]
[TD] 2.06[/TD]
[TD] 3,045,883[/TD]
[TD] 6,532,604.32[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,020.00[/TD]
[TD] 82,361.16[/TD]
[TD] 2.06[/TD]
[TD] 3,085,903[/TD]
[TD] 6,614,965.48[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 29,760.00[/TD]
[TD] 61,246.08[/TD]
[TD] 2.06[/TD]
[TD] 3,115,663[/TD]
[TD] 6,676,211.56[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 60,000.00[/TD]
[TD] 123,480.00[/TD]
[TD] 2.06[/TD]
[TD] 3,175,663[/TD]
[TD] 6,799,691.56[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 31,440.00[/TD]
[TD] 64,703.52[/TD]
[TD] 2.06[/TD]
[TD] 3,207,103[/TD]
[TD] 6,864,395.08[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 43,360.00[/TD]
[TD] 89,234.88[/TD]
[TD] 2.06[/TD]
[TD] 3,250,463[/TD]
[TD] 6,953,629.96[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 31,840.00[/TD]
[TD] 65,526.72[/TD]
[TD] 2.06[/TD]
[TD] 3,282,303[/TD]
[TD] 7,019,156.68[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 42,880.00[/TD]
[TD] 88,247.04[/TD]
[TD] 2.06[/TD]
[TD] 3,325,183[/TD]
[TD] 7,107,403.72[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 39,220.00[/TD]
[TD] 77,420.28[/TD]
[TD] 1.97[/TD]
[TD] 3,364,403[/TD]
[TD] 7,184,824.00[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,300.00[/TD]
[TD] 100,348.50[/TD]
[TD] 2.00[/TD]
[TD] 3,414,703[/TD]
[TD] 7,285,172.50[/TD]
[TD] 2.13[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,000.00[/TD]
[TD] 99,750.00[/TD]
[TD] 2.00[/TD]
[TD] 3,464,703[/TD]
[TD] 7,384,922.50[/TD]
[TD] 2.13[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 50,840.00[/TD]
[TD] 104,628.72[/TD]
[TD] 2.06[/TD]
[TD] 3,515,543[/TD]
[TD] 7,489,551.22[/TD]
[TD] 2.13[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 49,500.00[/TD]
[TD] 101,871.00[/TD]
[TD] 2.06[/TD]
[TD] 3,565,043[/TD]
[TD] 7,591,422.22[/TD]
[TD] 2.13[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,120.00[/TD]
[TD] 82,566.96[/TD]
[TD] 2.06[/TD]
[TD] 3,605,163[/TD]
[TD] 7,673,989.18[/TD]
[TD] 2.13[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (13,880.00)[/TD]
[TD] (21,861.00)[/TD]
[TD] 1.58[/TD]
[TD] 3,591,283[/TD]
[TD] 7,652,128.18[/TD]
[TD] 2.13[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (40,660.00)[/TD]
[TD] (64,039.50)[/TD]
[TD] 1.58[/TD]
[TD] 3,550,623[/TD]
[TD] 7,588,088.68[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (34,840.00)[/TD]
[TD] (54,873.00)[/TD]
[TD] 1.58[/TD]
[TD] 3,515,783[/TD]
[TD] 7,533,215.68[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (40,500.00)[/TD]
[TD] (63,787.50)[/TD]
[TD] 1.58[/TD]
[TD] 3,475,283[/TD]
[TD] 7,469,428.18[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] (40,820.00)[/TD]
[TD] (64,291.50)[/TD]
[TD] 1.58[/TD]
[TD] 3,434,463[/TD]
[TD] 7,405,136.68[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 13,880.00[/TD]
[TD] 27,690.60[/TD]
[TD] 2.00[/TD]
[TD] 3,448,343[/TD]
[TD] 7,432,827.28[/TD]
[TD] 2.16[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,660.00[/TD]
[TD] 81,116.70[/TD]
[TD] 2.00[/TD]
[TD] 3,489,003[/TD]
[TD] 7,513,943.98[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 34,840.00[/TD]
[TD] 69,505.80[/TD]
[TD] 2.00[/TD]
[TD] 3,523,843[/TD]
[TD] 7,583,449.78[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,500.00[/TD]
[TD] 80,797.50[/TD]
[TD] 2.00[/TD]
[TD] 3,564,343[/TD]
[TD] 7,664,247.28[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 40,820.00[/TD]
[TD] 81,435.90[/TD]
[TD] 2.00[/TD]
[TD] 3,605,163[/TD]
[TD] 7,745,683.18[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 48,880.00[/TD]
[TD] 97,515.60[/TD]
[TD] 2.00[/TD]
[TD] 3,654,043[/TD]
[TD] 7,843,198.78[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 19,920.00[/TD]
[TD] 39,740.40[/TD]
[TD] 2.00[/TD]
[TD] 3,673,963[/TD]
[TD] 7,882,939.18[/TD]
[TD] 2.15[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 9,340.00[/TD]
[TD] 16,181.55[/TD]
[TD] 1.73[/TD]
[TD] 3,683,303[/TD]
[TD] 7,899,120.73[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 9,640.00[/TD]
[TD] 16,701.30[/TD]
[TD] 1.73[/TD]
[TD] 3,692,943[/TD]
[TD] 7,915,822.03[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 9,020.00[/TD]
[TD] 15,627.15[/TD]
[TD] 1.73[/TD]
[TD] 3,701,963[/TD]
[TD] 7,931,449.18[/TD]
[TD] 2.14[/TD]
[/TR]
[TR]
[TD]308751[/TD]
[TD] 9,120.00[/TD]
[TD] 15,800.40[/TD]
[TD] 1.73[/TD]
[TD] 3,711,083[/TD]
[TD] 7,947,249.58[/TD]
[TD] 2.14[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

---

 

[jondavis1987]

Will this work for you? =SUMPRODUCT(--(A:A=L5),B:B,D:D)/SUMPRODUCT(--(A:A=L5),B:B) I used L5 as where i typed in the material ID. Column A is the material, B is quanity, D is price

 

[AhoyNC]

Maybe:

Excel Workbook
	A	B	C	D	E	F	G
1	Material ID	308751
2	Average Price	2.142
3
4	Material ID	Quantity	Value	Price	Cummulative volume	Ending Value Cumm	Ending Price
5	308751	50,480.00	116,104.00	2.3	50,480	116,104.00	2.3
6	308751	50,240.00	115,552.00	2.3	100,720	231,656.00	2.3
7	308751	50,040.00	115,092.00	2.30	150,760	346,748.00	2.3
8	308751	50,260.00	115,598.00	2.30	201,020	462,346.00	2.3
9	308751	50,620.00	116,426.00	2.30	251,640	578,772.00	2.3
10	308751	45,160.00	103,868.00	2.30	296,800	682,640.00	2.3
117	308751	9,640.00	16,701.30	1.73	3,692,943	7,915,822.03	2.14
118	308751	9,020.00	15,627.15	1.73	3,701,963	7,931,449.18	2.14
119	308751	9,120.00	15,800.40	1.73	3,711,083	7,947,249.58	2.14
Sheet

 

[marius m]

Thanks Jondavis1987. This works perfectly.

 

[marius m]

Thanks AhoyNC, that works. I did change the hardcoding assigned to the B2 cell as there are many different material IDs involved, but other than that everything is good. Thank you

 

[AhoyNC]

Actual there is a problem with the formula I posted with the different ID's. Instead of SUM I should have used the SUMIF function.
So, my formula in post#3 should be:

[TABLE="width: 700"]
<colgroup><col width="700"></colgroup><tbody>[TR]
   [TD="width: 700"]SUMPRODUCT(--($A$5:$A$119=$B$1),$B$5:$B$119,$D$5:$D$119)/SUMIF($A$5:$A$119,$B$1,$B$5:$B$119)[/TD]
 [/TR]
</tbody>[/TABLE]

Change ranges to match your data.