Vlook up issue

[bigry26]

I need some help with a formula. In theory, multiplying weight supposed to be in bags x bags = total weight in bags but we used more than the theory number of lbs. The difference between what we were supposed to used and actually used is scrap. I need to find out what the scrap and yields are but I'm not getting my numbers to jive. I need to write a formula that looks up the part data and autofills how much was used in reality in order to get a yield and scrap. Any help would be appreciated.

[TABLE="width: 618"]
<colgroup><col><col><col><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Part #[/TD]
[TD]Lbs[/TD]
[TD][/TD]
[TD]Part #[/TD]
[TD]Lbs/bag[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/22/2018[/TD]
[TD="align: right"] 4/23/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4/22/2018 [/TD]
[TD]ABC[/TD]
[TD="align: right"]4,522[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD]Bags[/TD]
[TD="align: right"]5,300[/TD]
[TD="align: right"]3,800[/TD]
[/TR]
[TR]
[TD="align: right"]4/22/2018[/TD]
[TD] DEF[/TD]
[TD="align: right"]9,871[/TD]
[TD][/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD].75[/TD]
[TD]Lbs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4/22/2018 [/TD]
[TD]ABC[/TD]
[TD="align: right"]8,745[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4/23/2018 [/TD]
[TD]ABC[/TD]
[TD="align: right"]4,561[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]theory[/TD]
[TD="align: right"]2,650[/TD]
[TD="align: right"]1,900[/TD]
[/TR]
[TR]
[TD="align: right"]4/23/2018 [/TD]
[TD]DEF[/TD]
[TD="align: right"]5,687[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]reality[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4/23/2018[/TD]
[TD] ABC[/TD]
[TD="align: right"]1,358[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yield[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Try using SUMIFS.
The example below adds the weight for ABC in your lbs column for each date.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Date	Part #	Lbs		Part #	Lbs/bag			4/22/2018	4/23/2018
2	4/22/2018	ABC	4,522		ABC	0.5		Bags	5,300	3,800
3	4/22/2018	DEF	9,871		DEF		0.75	Lbs
4	4/22/2018	ABC	8,745					ABC
5	4/23/2018	ABC	4,561					theory	2,650	1,900
6	4/23/2018	DEF	5,687					reality	13267	5919
7	4/23/2018	ABC	1,358					Yield
Sheet

 

[bigry26]

AhoyNC,

I'm not sure if I did something wrong but I got a - ... for my result.

 

[AhoyNC]

What ver. of Excel are you using (SUMIFS is in Excel 2010 and higher)?

 

[bigry26]

I'm using excel 2016.

 

[AhoyNC]

If your cell is formatted as "accounting" and SUMIFS returns 0 it will show up as -.
-Check your ranges.
-Make sure your dates are actual Excel dates and not text (both the dates in the column Date and your look up date).
If that doesn't work post a copy of the formula you are using and whats in the different columns (ex. which column holds the date, lbs, etc.

 

[bigry26]

Thank you!