Selected Answer
You will need to add a helper column. This column must contain a formula to extract the quantity. You can hide it. This is the formula it should contain.
=IFERROR(MID($A2,FIND(",",$A2)+1,6),1)*1
Note that the 6 in the formula expresses the number of characters to be extracted. The presumption is that nothing follows the number you want. Excel will ignore the instruction to extract non-existant characters but if you have very large numbers, like 123456789, the final digits will be truncated.
The formula modifies the result in two ways. One, an error wil occur if no comma is found. In that case the quantity will be presumed to be 1. Two, the result is multiplied with 1. This is necessary because the extracted part of A2 is a string (caused by the formula, irrespective of cell formatting) which can't be used in calculations. Multiplying it with 1 forces Excel to convert it to a number.
I added a column B in your Scan-Out sheet (same for the Scan-In). Note that it is necessary to format this column as "General". Throughout your workbook cells are formatted as "Text". I recommend changing that. If you don't know what to format a cell as, your first choice should be "General" which will cause Excel to determine the most suitable format based on what you enter.
Now that you have the quantities you want to add up in column B, sum them up in column E (that was column D before inserting the extra column B) using the formula below.
=SUMIFS($B$2:$B$200,$A$2:$A$200,$D2&"*")
Note that the format in column E must also be "General" or a number format, not "Text".
I recommend that you review all your formulas. Formulas like =COUNTIF($A$2:$A$199,$D$2:$D$200), with differently sized ranges, will cause errors. Formulas like =(INVENTORY!A2:A182) are all but useless. Observe that it changes to =(INVENTORY!A2:A183) in the row below and =(INVENTORY!A202:A382) in the table's last row. Frankly, I don't know how it works and would appreciate your teaching me. However, it probably must be =(INVENTORY!$A$2:$A$183), with dollar signs to prevent the range from shifting when copied down.
Your workbook contains a lot of tables, some of them deleted but not removed. Look at the Name Manager (Formulas tab). Consider removing all tables. Tables are helpful for some advanced tasks. I feel that you might actually be better off without them. They just add rules to the ways you must work.
Finally, I saw that the code in your workbook is highly questionable. I tried to improve it but gave up. In principle, you tried to create tables with formulas copied down as far as you think you might need. This behaviour is anti-automation. It is designed not to require automation. Therefore automation will have difficulty working with it. If you wish for more automation in the future, get rid of everything that makes it unnecessary.
