# Using SumIf & VBA to Account for Multiple Instances of Same Part # in Bill of Materials

1. ## Using SumIf & VBA to Account for Multiple Instances of Same Part # in Bill of Materials

Hello,

Attaching a sample workbook to give an idea of what I am trying to accomplish - trying to use the SumIf function via VBA to help automate a build / bill of materials (BOM) process that allows us to figure out how many of each part we need to order for an upcoming build. In our BOM, we use the same part in different subassemblies, meaning the same part may show up multiple times throughout the entire BOM with a different pick quantity for each subassembly. i.e. we might use 1 given resistor in 1 subassembly and 2 more in a separate subassembly for the same build. Reason we would like to automate this is so that when we have future orders, we want to enter Build Quantity and have the sheet display how many of each unique part we need to order, accounting for all instances of each part across the subassemblies.

Below in some word math:
total BOM quantity of a specific part number = pick quantity of said part in subassembly 1 + pick quantity of same part in subassembly 2 + ...

number to order = total BOM quantity of said part number - current inventory quantity on hand

I am using a For loop to iterate through the part numbers in a given BOM and understand (I think) the SumIf function is what I need. But then once all instances of a part have been found and the # to order is determined, as the loop iterates through the remaining part numbers it will also need to skip the lines of part numbers that have already been accounted for above. Does this make sense? Attaching sample workbook for reference. Thank you in advance for any feedback / direction - greatly appreciated.

2. ## Re: Using SumIf & VBA to Account for Multiple Instances of Same Part # in Bill of Material

Is it simply like this?
``Please Login or Register  to view this content.``

3. ## Re: Using SumIf & VBA to Account for Multiple Instances of Same Part # in Bill of Material

What about Pivot Table, see file attached

4. ## Re: Using SumIf & VBA to Account for Multiple Instances of Same Part # in Bill of Material

@kbm234

Windows 10 is your OS, not your Office version - please update your profile.: )

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1