# Calculating prices issue

#### thunder_anger

##### Board Regular
Hi to all of you and good morning
I have an issue here
I want to calculate the cost of sold items according to the stock and the price according to date
 Code QTY U. Price Date 301 3 0.85 01/01/2012 301 15 0.6 06/01/2012 301 20 0.5 13/01/2012 402 50 4.5 06/05/2012 402 30 2.5 06/06/2012 305 120 2.5 1/1/2013 301 100 0.25 01/01/2013 385 25 25 1/1/2013 310 25 25 1/1/2013 301 25 1.1 6/2/2013 320 222 0.3 6/2/2013

<tbody>
</tbody>

If I sold 100 unit of the product coded 301 I want it to calculate the price as follows
3*0.85 + 15*0.6 + 20*0.5 + 62*0.25
Is that possible

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### erisnake

##### New Member
Hi can you try this:
=SUMPRODUCT(--(A:A=301),B:B,C:C)

A:A = Code column
B:B = Qty column
C:C = Price column

Then you change the condition depending on the Code you want to get the sum product from.

#### Firefly2012

##### Well-known Member
The following formula in K2 can be usedf but it does require helper columns appended to your existing data. The advantage of using such an approach is that it scales well if you have many different purchase dates for the underlying Code lines:

Excel 2007
ABCDEFGHIJK
1CodeQTYU. PriceDateBoundary QtyUnit Price DiffCodeQtyTotal Price
230130.8501/01/201200.8530110037.05
3301150.606/01/20123-0.25
4301200.513/01/201218-0.1
5402504.506/05/201204.5
6402302.506/06/201250-2
73051202.501/01/201302.5
83011000.2501/01/201338-0.25
9385252501/01/2013025
10310252501/01/2013025
11301251.106/02/20131380.85
123202220.306/02/201300.3
Sheet1
Cell Formulas
RangeFormula
E2=SUMIF(\$A\$1:\$A1,\$A2,\$B\$1:\$B1)
F2=C2-IFERROR(LOOKUP(2,1/(\$A\$1:A1=A2),\$C\$1:C1),0)
K2=SUMPRODUCT((\$A\$2:\$A\$12=I2)+0,(\$E\$2:\$E\$12)+0,J2-\$E\$2:\$E\$12,\$F\$2:\$F\$12)
Formulas in E2 and F2 are copied down as far as required.

Last edited:

#### thunder_anger

##### Board Regular
The following formula in K2 can be usedf but it does require helper columns appended to your existing data. The advantage of using such an approach is that it scales well if you have many different purchase dates for the underlying Code lines:

Excel 2007
ABCDEFGHIJK
1CodeQTYU. PriceDateBoundary QtyUnit Price DiffCodeQtyTotal Price
230130.8501/01/201200.8530110037.05
3301150.606/01/20123-0.25
4301200.513/01/201218-0.1
5402504.506/05/201204.5
6402302.506/06/201250-2
73051202.501/01/201302.5
83011000.2501/01/201338-0.25
9385252501/01/2013025
10310252501/01/2013025
11301251.106/02/20131380.85
123202220.306/02/201300.3

</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=SUMIF(\$A\$1:\$A1,\$A2,\$B\$1:\$B1)
F2=C2-IFERROR(LOOKUP(2,1/(\$A\$1:A1=A2),\$C\$1:C1),0)
K2=SUMPRODUCT((\$A\$2:\$A\$12=I2)+0,(\$E\$2:\$E\$12<J2)+0,J2-\$E\$2:\$E\$12,\$F\$2:\$F\$12)

</tbody>

<tbody>
</tbody>

Formulas in E2 and F2 are copied down as far as required.
what is Boundary Qty

#### thunder_anger

##### Board Regular
Can I Make a new col as the new stock to clarify the new changes in stock
Excel 2007
ABCDEFGHIJK
1CodeQTYU. PriceDateBoundary QtyUnit Price DiffNew StockCodeQtyTotal Price
230130.8501/01/201200.85030110037.05
3301150.606/01/20123-0.250
4301200.513/01/201218-0.10
5402504.506/05/201204.550
6402302.506/06/201250-230
73051202.501/01/201302.5120
83011000.2501/01/201338-0.2562
9385252501/01/201302525
10310252501/01/201302525
11301251.106/02/20131380.8525
123202220.306/02/201300.3222

</tbody>
Sheet

#### erisnake

##### New Member
Hi,

Why the sumproduct formula does not fit your needs?

#### Firefly2012

##### Well-known Member
erisnake - I presume the OP wants the price calculated on a First In First Out basis which your origianl formula does not do.

thunder_anger - what's the purpose of the New Stock column?

Also, I don't make use of the Date column in my original formula - how would you want this factored in? Presumably, you need to derive the price for X amount of stock given that Y amount has already been sold by that point (so the FIFO price will have moved from its starting point)?

Can you provide a few examples of how you want this to work (it makes it much easier to visualise how the formula should work then)?

#### thunder_anger

##### Board Regular
erisnake - I presume the OP wants the price calculated on a First In First Out basis which your origianl formula does not do.

thunder_anger - what's the purpose of the New Stock column?

Also, I don't make use of the Date column in my original formula - how would you want this factored in? Presumably, you need to derive the price for X amount of stock given that Y amount has already been sold by that point (so the FIFO price will have moved from its starting point)?

Can you provide a few examples of how you want this to work (it makes it much easier to visualise how the formula should work then)?
you under stood what i mean
i calculate the costs of manufacturing a hand made product
it is assembled by about 1259 piece
the store that i put the parts in is like you saw in the example every purchase is written by part code and cost in the time of purchase
i want to calculate the prices of used parts and to generate the new stock after building my machine

Replies
3
Views
133
Replies
5
Views
207
Replies
8
Views
350
Replies
2
Views
291
Replies
6
Views
152

1,195,719
Messages
6,011,290
Members
441,599
Latest member
Jribas

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