Automatic Look up and count all parts that have been built

You have "dirty" data. Each of the part numbers in Column A has a trailing CHAR(160) that looks like a space, but isn't. So this has to be cleaned off before we can use it in a formula.

I converted both of your data ranges into Excel Tables since tables know how big they are so formulas that reference them always consider exactly the right amount of data and formulas in tables copy down automatically and you can used column headers in formulas to make it easier to understand and to debug the formulas.

The summary formula you want is: =SUMIFS(Table_Data[Loc Qty Change],Table_Data[Item Number],SUBSTITUTE([@Part],CHAR(160),""))

Here is more information on using tables: http://www.utteraccess.com/wiki/Tables_in_Excel.