Excel 2007 : I need help with formulas that allow me to not double count.

[Corporateguy]

I have 5 total spreadsheets. The first and main sheet is a recommended list of parts that i gave to a customer. The next four sheets are lists of parts that the customer ordered from my recommended list.

I have a unique identifier, but the customer ordered parts randomly. My mission is to count what the customer order, but create a delta, so that i can see what he hasnt. It sounds simple at first. Where i run into trouble is that these parts are listed numerous times in different line items because they are for different services. I realize i could just consolidate the entire list and just match totals, but i have to have it detailed by line item.

I have been using the sumifs function, but that counts my unique identifier multiple times and does not give me an accurate quantity because it is referencing the same quantity everytime that unique identifier is listed in my master list. I know this sounds hella confusing. I am having a hard time even putting it in words. Is there a formula that will allow me to compare quantities between two sheets without double counting the same part? In the first list i have a total of items the customer ordered. In my first compare i just want to pull from that quantity and make sure its zero. If you can understand me or have the patience to want to help...please do so. I need it!!!

[FDibbins]

Could you upload a sample workbook with some dummy data in it, along with what kind of output you would expect?