Sumproduct lookups

[salahmost]

Hi,

I have a sumproduct formula :

=SUMPRODUCT(SUMIF('Price List'!A2:A1400,Sales!C2:C5000,'Price List'!D2:D1400),Sales!H2:H5000)

This Formula gives me the sales amount depending on Price list sheet Where:
A2: A1400 is the item code in price list sheet
D4:D1400 is the price in price list sheet

C2:C5000 is the code for the sold items in Sales sheet.
H2:H5000 is the quantities of sold items in Sales sheet.
The good thing that I can add more criteria to this formula as getting sales for a specific company ....etc.
Now a new thing has come : sometimes a company issues a new purchase order for items with a different price .... I can add the same item code with this price to the price list ,and this P.O. number to sales ...How can I modify my formula to count the sales with items that have different prices for the the same product code?

Thank you
____________________________
Salah

 

[salahmost]

any Suggestions even with another approach ?

 

[Akashwani]

Hi,

Could you provide some sample data?
Where do you want the result displaying?

Excel Workbook
	A	B	C	D
1	ID Number	*	*	Amount
2	AA10000	*	*	368.00
3	AA10001	*	*	277.00
4	AA10002	*	*	474.00
5	AA10003	*	*	163.00
6	AA10004	*	*	330.00
7	AA10005	*	*	346.00
8	AA10006	*	*	472.00
9	AA10007	*	*	343.00
10	AA10008	*	*	300.00
11	AA10009	*	*	343.00
12	AA10010	*	*	100.00
13	AA10011	*	*	150.00
14	AA10012	*	*	147.00
15	AA10013	*	*	267.00
16	AA10014	*	*	458.00
17	AA10015	*	*	436.00
Price List

Excel Workbook
	C	D	E	F	G	H	I
1	Sold Items					Quantity	Value of Sales
2	AA10001					5	1,385.00
3	AA10002					5	2,370.00
4	AA10003					2	326.00
5	AA10004					2	660.00
6	AA10005					1	346.00
7	AA10006					1	472.00
8	AA10007					5	1,715.00
9	AA10008					2	600.00
10	AA10009					1	343.00
11	AA10010					2	200.00
Sales

Ak

 

[Akashwani]

Hi Salah,

Is it possible to add a "Helper" column to each sheet?

Excel Workbook
	A	B	C	D	E	F
1	ID Number	*	*	Amount	*	Helper
2	AA10000	*	*	300.00	*	AA10000-1
3	AA10001	*	*	100.00	*	AA10001-1
4	AA10002	*	*	400.00	*	AA10002-1
5	AA10003	*	*	100.00	*	AA10003-1
6	AA10004	*	*	300.00	*	AA10004-1
7	AA10005	*	*	300.00	*	AA10005-1
8	AA10006	*	*	400.00	*	AA10006-1
9	AA10007	*	*	300.00	*	AA10007-1
10	AA10008	*	*	300.00	*	AA10008-1
11	AA10009	*	*	300.00	*	AA10009-1
12	AA10010	*	*	100.00	*	AA10010-1
13	AA10001	*	*	150.00	*	AA10001-2
14	AA10012	*	*	150.00	*	AA10012-1
15	AA10013	*	*	250.00	*	AA10013-1
16	AA10014	*	*	450.00	*	AA10014-1
17	AA10015	*	*	400.00	*	AA10015-1
Price List

Excel Workbook
	C	D	E	F	G	H	I	J	K
1	Sold Items	*	*	*	*	Quantity	Value of Sales	*	Helper
2	AA10000	*	*	*	*	5	1,500.00	*	AA10000-1
3	AA10001	*	*	*	*	5	500.00	*	AA10001-1
4	AA10002	*	*	*	*	2	800.00	*	AA10002-1
5	AA10003	*	*	*	*	2	200.00	*	AA10003-1
6	AA10004	*	*	*	*	1	300.00	*	AA10004-1
7	AA10005	*	*	*	*	1	300.00	*	AA10005-1
8	AA10006	*	*	*	*	5	2,000.00	*	AA10006-1
9	AA10007	*	*	*	*	2	600.00	*	AA10007-1
10	AA10008	*	*	*	*	1	300.00	*	AA10008-1
11	AA10009	*	*	*	*	2	600.00	*	AA10009-1
12	AA10010	*	*	*	*	4	400.00	*	AA10010-1
13	AA10001	*	*	*	*	4	600.00	*	AA10001-2
14	AA10012	*	*	*	*	4	600.00	*	AA10012-1
15	AA10013	*	*	*	*	4	1,000.00	*	AA10013-1
16	AA10014	*	*	*	*	4	1,800.00	*	AA10014-1
17	AA10015	*	*	*	*	5	2,000.00	*	AA10015-1
Sales

The formulas need to be copied down.

That's the only thing that comes to mind, sorry.

Ak

 

[snoopyhr]

Maybe this ?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Item-code	Price	QTY.	Sales		Item of interest	ITME-1
2	ITME-1	$ 645.00	25	$16,125.00		COUNT OF THIS	4
3	ITME-3	$ 319.00	33	$10,527.00
4	ITME-1	$ 369.00	29	$10,701.00		Price	QTY.	Sales
5	ITME-1	$ 660.00	20	$13,200.00		$ 645.00	25	$16,125.00
6	ITME-3	$ 734.00	32	$23,488.00		$ 369.00	29	$10,701.00
7	ITME-2	$ 493.00	32	$15,776.00		$ 660.00	20	$13,200.00
8	ITME-2	$ 246.00	47	$11,562.00		$ 718.00	27	$19,386.00
9	ITME-1	$ 718.00	27	$19,386.00
10	ITME-2	$ 165.00	40	$ 6,600.00
11	ITME-2	$ 183.00	45	$ 8,235.00
Sheet2

 

[salahmost]

Thanks for your help.
The sales Sheet doesn't contain formulas and the desired result is obtained from 1 formula.
Here is the some sample data:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Item Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Item</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Company</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">PO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #FF0000;;">205180001</td><td style="font-weight: bold;border-top: 1px solid black;color: #FF0000;;">Spoon KFC</td><td style="font-weight: bold;border-top: 1px solid black;color: #FF0000;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #FF0000;;">1</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #FF0000;;">2589</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">205180013</td><td style=";">Plastic Fork</td><td style=";">KFC</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">2595</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">205180003</td><td style=";">Baskin 5 inch Spoon</td><td style=";">KFC</td><td style="text-align: right;;">1.25</td><td style="text-align: right;;">2595</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">205180004</td><td style=";">Hardes Spoon</td><td style=";">KFC</td><td style="text-align: right;;">1.75</td><td style="text-align: right;;">2596</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">205103030</td><td style=";">Egg Tray Df40 </td><td style=";">Electro Lux</td><td style="text-align: right;;">1.8</td><td style="text-align: right;;">698789</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">205103031</td><td style=";">Freezer Shelf Gs</td><td style=";">Electro Lux</td><td style="text-align: right;;">6.5</td><td style="text-align: right;;">698801</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">205103032</td><td style=";">Frame For Shelf Glass Ref Df</td><td style=";">Electro Lux</td><td style="text-align: right;;">6.8</td><td style="text-align: right;;">698805</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">205103033</td><td style=";">Chilled Room Tray</td><td style=";">Electro Lux</td><td style="text-align: right;;">14.55</td><td style="text-align: right;;">698814</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">205103038</td><td style=";">Cover For Chill Without</td><td style=";">Electro Lux</td><td style="text-align: right;;">12</td><td style="text-align: right;;">698889</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">205103039</td><td style=";">Guard Variety B</td><td style=";">Electro Lux</td><td style="text-align: right;;">2.8</td><td style="text-align: right;;">698947</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">PriceList</p><br /><br />

Cannot add the changed price item to the price list as it will give a wrong result:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Item Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Item</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Company</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #333399;;">PO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #FF0000;;">205180001</td><td style="font-weight: bold;border-top: 1px solid black;color: #FF0000;;">Spoon KFC</td><td style="font-weight: bold;border-top: 1px solid black;color: #FF0000;;">KFC</td><td style="text-align: right;border-top: 1px solid black;;">1.25</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #FF0000;;">2601</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">PriceList</p><br /><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Item Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Company</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Item Name</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Sold</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">PO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">11/04/04</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">205180001</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">Spoon KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">250000</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">2589</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/04</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205180004</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Hardes Spoon</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">30</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/06</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205103039</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Electro Lux</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Guard Variety B</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">800</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/04</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205180013</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Plastic Fork</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">1200</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/04</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205180003</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Baskin 5 inch Spoon</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">2400</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/05</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205103030</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Electro Lux</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Egg Tray Df40 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">840</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/05</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205103038</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Electro Lux</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Cover For Chill Without</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">680</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/04</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205180004</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Hardes Spoon</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">30</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/05</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205103031</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Electro Lux</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Freezer Shelf Gs</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">380</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">11/04/05</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">205180001</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">Spoon KFC</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">300000</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #FF0000;background-color: #FFFF99;;">2601</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/05</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205103032</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Electro Lux</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Frame For Shelf Glass Ref Df</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;">90</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;color: #0000FF;background-color: #FFFF99;;">11/04/05</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #0000FF;background-color: #FFFF99;;">205103033</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Electro Lux</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #0000FF;background-color: #FFFF99;;">Chilled Room Tray</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #0000FF;background-color: #FFFF99;;">560</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;color: #0000FF;background-color: #FFFF99;;"></td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sales</p><br /><br />

Formula:
=SUMPRODUCT(SUMIF(A2:A11,Sales!B2:B13,D2:D11),Sales!E2:E13)

Result before Item "Spoon KFC" price change: 578047

Thank you

 

[Akashwani]

Hi Salah,

I still don't know where or how you want to display your result.
Is this what you require?.....

Excel Workbook
	A	B	C	D	E	F
1	Date	Item Code	Company	Item Name	Sold	PO
2	11/04/2004	205180001	KFC	Spoon KFC	250000	2589
3	11/04/2004	205180004	KFC	Hardes Spoon	30	2596
4	11/04/2006	205103039	Electro Lux	Guard Variety B	800	698947
5	11/04/2004	205180013	KFC	Plastic Fork	1200	2595
6	11/04/2004	205180003	KFC	Baskin 5 inch Spoon	2400	2595
7	11/04/2005	205103030	Electro Lux	Egg Tray Df40	840	698789
8	11/04/2005	205103038	Electro Lux	Cover For Chill Without	680	698889
9	11/04/2004	205180004	KFC	Hardes Spoon	30	2596
10	11/04/2005	205103031	Electro Lux	Freezer Shelf Gs	380	698801
11	11/04/2005	205180001	KFC	Spoon KFC	300000	2601
12	11/04/2005	205103032	Electro Lux	Frame For Shelf Glass Ref Df	90	698805
13	11/04/2005	205103033	Electro Lux	Chilled Room Tray	560	698814
Sales

Excel Workbook
	A	B	C	D	E	F
1	Item Code	Item	Company	Price	PO	Result
2	205180001	Spoon KFC	KFC	1	2589	250000
3	205180013	Plastic Fork	KFC	1.5	2595	1800
4	205180003	Baskin 5 inch Spoon	KFC	1.25	2595	3000
5	205180004	Hardes Spoon	KFC	1.75	2596	105
6	205103030	Egg Tray Df40	Electro Lux	1.8	698789	1512
7	205103031	Freezer Shelf Gs	Electro Lux	6.5	698801	2470
8	205103032	Frame For Shelf Glass Ref Df	Electro Lux	6.8	698805	612
9	205103033	Chilled Room Tray	Electro Lux	14.55	698814	8148
10	205103038	Cover For Chill Without	Electro Lux	12	698889	8160
11	205103039	Guard Variety B	Electro Lux	2.8	698947	2240
12	205180001	KFC	Spoon KFC	1.25	2601	375000
PriceList

I hope that works for you, if not, take a look here.....

http://www.youtube.com/user/ExcelIsFun#p/search/43/yQv2QFlbzCI

Ak

 

[salahmost]

Thanks again for your help
My original formula calculate the sold items( many different items) ( Not all the items in Price list)...In one formula ( In another sheet not sales or priceList sheet)
for each sold items multiplied by its lookup price to give you the total ) the item maybe mentioned more than 1 time.
please review my sample data, The problem occurs when the price changed for some items!
so the changed items have more than 1 lookup.

Thanks

Salah

 

[Akashwani]

Have you tried to modify this formula?

=SUMPRODUCT((Sales!$B$2:$B$13=A2)*(Sales!$F$2:$F$13=E2)*Sales!$E$2:$E$13)*D2

It is checking if the Item code and PO number are in the Sales range.
Then it adds up the number of Items sold then multiplies the rest by the Price.

I'm sorry if this isn't the solution you require.
To get the result you require, I suggest that you post a sample of you expected result to compliment the sample data you have already provided.

Ak