Calculating prices issue

[thunder_anger]

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

 

[erisnake]

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Code	QTY	U. Price	Date	Boundary Qty	Unit Price Diff			Code	Qty	Total Price
2	301	3	0.85	01/01/2012	0	0.85			301	100	37.05
3	301	15	0.6	06/01/2012	3	-0.25
4	301	20	0.5	13/01/2012	18	-0.1
5	402	50	4.5	06/05/2012	0	4.5
6	402	30	2.5	06/06/2012	50	-2
7	305	120	2.5	01/01/2013	0	2.5
8	301	100	0.25	01/01/2013	38	-0.25
9	385	25	25	01/01/2013	0	25
10	310	25	25	01/01/2013	0	25
11	301	25	1.1	06/02/2013	138	0.85
12	320	222	0.3	06/02/2013	0	0.3
Sheet1

Cell Formulas
Range	Formula
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.

 

[thunder_anger]

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

	A	B	C	D	E	F	G	H	I	J	K
1	Code	QTY	U. Price	Date	Boundary Qty	Unit Price Diff			Code	Qty	Total Price
2	301	3	0.85	01/01/2012	0	0.85			301	100	37.05
3	301	15	0.6	06/01/2012	3	-0.25
4	301	20	0.5	13/01/2012	18	-0.1
5	402	50	4.5	06/05/2012	0	4.5
6	402	30	2.5	06/06/2012	50	-2
7	305	120	2.5	01/01/2013	0	2.5
8	301	100	0.25	01/01/2013	38	-0.25
9	385	25	25	01/01/2013	0	25
10	310	25	25	01/01/2013	0	25
11	301	25	1.1	06/02/2013	138	0.85
12	320	222	0.3	06/02/2013	0	0.3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formulas in E2 and F2 are copied down as far as required.

thanks for your reply >> but can I ask
what is Boundary Qty

 

[thunder_anger]

ADVERTISEMENT

Can I Make a new col as the new stock to clarify the new changes in stock
Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	Code	QTY	U. Price	Date	Boundary Qty	Unit Price Diff	New Stock		Code	Qty	Total Price
2	301	3	0.85	01/01/2012	0	0.85	0		301	100	37.05
3	301	15	0.6	06/01/2012	3	-0.25	0
4	301	20	0.5	13/01/2012	18	-0.1	0
5	402	50	4.5	06/05/2012	0	4.5	50
6	402	30	2.5	06/06/2012	50	-2	30
7	305	120	2.5	01/01/2013	0	2.5	120
8	301	100	0.25	01/01/2013	38	-0.25	62
9	385	25	25	01/01/2013	0	25	25
10	310	25	25	01/01/2013	0	25	25
11	301	25	1.1	06/02/2013	138	0.85	25
12	320	222	0.3	06/02/2013	0	0.3	222

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet​

 

[erisnake]

Hi,

Why the sumproduct formula does not fit your needs?

 

[Firefly2012]

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]

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