How to calculate a multi order BOM with Excel

Right-click on the worksheet tab – move or copy;

Select Move to "New Workbook", and select "Make Copy" as Copy, and leave it unchecked as Move;

The above methods should also be moved or copied one by one, there is no way to do it in one go, all scattered and disassembled, in fact, it is not necessary, it can be merged into a workbook as much as possible into a workbook, easy to find and reference.

Each GG mm of BOM may be very distressed when checking BOM, facing whether the amount of each material is consistent with the actual position number, I will give a function formula to solve!

Illustrate. a b c

A1 Serial number no Position number designator ref Usage qty

a2 1 r1,r2,r3,r4,r5,r6,r7,r9,r13,r25 10

a3 2 c1,c3,c5,c7,c8,c15,c25,c30 8

You can use the functions substitute and len formulas to calculate the number of position numbers, and then compare them with the usage bar (if).

Formula: =len(b2)-len(substitute(b2,",",""))+1

Note: This is to find the comma (,) in the cell if it is a space or other universal symbol, change it the same.

I don't quite understand, take the problem as the content (the subject of the email must contain "excel", I use this as a basis to identify non-spam, so as not to delete it by mistake), and the excel sample file (try to explain the current situation and target effect in detail) as an attachment to help you see.

Enter the following formula in cell d10 and fill the formula down.

if(a10="","",c10*vlookup(a10,b$2:c$4,2,0))

You can choose to summarize by PivotTable or Conditional Summation.

If it's just a single-layer BOM, that's the simplest VLOOKUP formula with addition, subtraction, multiplication and division, I've done one before, it's very simple, and Excel can understand a little. Multi-level or unlimited, if the amount of data is large, I don't think I can afford Excel calculations.

Try using a pivot table to meet your requirements.

First of all, you need to determine which system your BOM is ultimately used for, if it is only for your own internal use (for the purchasing department), you only need to make the purchased parts into the **, indicating the quantity, specifications, usage, etc. If it is for the system service (sales, production, purchasing, quality department joint service), it is necessary to understand the requirements of the system, if necessary, to do a layered BOM, that is, raw materials - semi-finished products - assembly parts and so on. Secondly, after making a product BOM, you can save this format, copy and paste the format down.

However, it is necessary to check the BOM in detail, and this process is easy to make mistakes. The above is just a simple explanation, and it takes a lot of thought to do it specifically.

It is estimated that there are few people who know what the requirements of BOM calculation are, so you had better describe the specific summary requirements, and there may be more people who answer.

Is it the sum of the quantity * cost according to the level of the level?

This can be a template, but with a BOM as a parameter table. The usage table is then automatically generated based on the number of products.

A bill of materials (item name, unit price, quantity, inventory).

A product BOM (product name, item name, and quantity).

A costing table (product name, quantity, cost price).

How do multiple BOMs exist? Is it multiple workbooks, or multiple worksheets in one workbook, or multiple regions in one worksheet? Or is it a mix of the above?