Summarizing a large sheet using pivot tables or other options

Hi All, Very simply i have a large worksheet that i use to track materials in a weld shop. Vertically i have all the material sizes we use (4" x 4" x 1/4" etc) about 100 in all, along with qty in stock. Across the top i have all the Bom names for products we make. Where the columns and rows dissect i input how much of the material that bom uses in inches.

I currently produce material shortage report using a pivot table for the purchasing manager. Total material required vs available.

Now ive been asked to summarize (on the same table if possible) what material is used by what Boms. The idea is that if we are short some material, we can quickly see what its used on and then prioritize one bom over another to build.

Im struggling to see how to do this as it involves a text result. Any suggestions appreciated.

Ok Thanks. ive attached a small sample sheet.

Currently using 2007 at work. Home on 365.