Matrix lookup and summing of components formula help

[kurtlee]

Good day.  I am trying to do some calculations on a matrix of products to assemble (column A) and the components they are comprised of (row 1).  Some of these components are used on multiple products.  What I would like to do is have 2 lists, 1 of the products to assemble during the day and the other a list of all components needed to accomplish the tasks.

I have the matrix completed and filled out with the number of each component required for assembly.  On another sheet I created a list of the products (column A) and Qty to build (column B) as well as a list of components (column G) and Qty needed for builds (column H).

I thought that it might be possible to use an IF statement to make sure column B was > 0, then do a lookup based off column A of both sheets and have it then match the component name in column g with row 1 of the matrix sheet.  Then it would need to sum all the components for all the products and possibly multiply them.

I was working on just getting the lookup portion working and am probably in over my head and could use help.  I have attached screenshots of a sample of what I'm working on, as I'm sure I didn't explain it very well. 

 

 

Thank you to anyone who might have better methods or can help in any way.

[#2]

@kurtlee 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@Hans Vogelaar 

Thank you for the reply.  I tried to attach an example when I originally posted but couldn't figure out how and still can't.  Below is a link to OneDrive and the example I took the screenshots from.

Example 

[#4 · Accepted solution]

@kurtlee 

The mathematical approach for this problem is to use MMULT for the matrix multiplication

= MMULT(TRANSPOSE(compsPerProduct), productQuantity)

Sadly the formula is unlikely to appeal to those without a STEM background.  There is an alternative using 365 array formulas.

= LET(
    componentsNeeded, productQuantity * compsPerProduct,
    neededOverall,    BYCOL(componentsNeeded, LAMBDA(x, SUM(x))),
    TOCOL(neededOverall)
  )

The first line simply multiplies the component table by the number of products required to determine the component count for each product.  Next, summing by column gives the overall component counts.  The result is a row of counts so the final step converts it to a column.  Fortunately the two methods give identical results.

[#5]

@Peter Bartholomew 

First and foremost, thank you.  It appears that the MMULT with the TRANSPOSE should fit perfectly for what is needed.

Secondly, thank you for showing me that you can define ranges of data to make it easier to call up when working with formulas, I never even realized that was possible.

I attempted to recreate your sample within my example linked from my OneDrive, but am just getting the #VALUE! error, so not sure exactly what I'm doing differently.

I'll check into it a little more tomorrow and see if I can figure out what's going on to get it working on my workbook.

[#6]

@kurtlee MMULT is a very picky function in that it does not fail gracefully.  If the number of product rows/columns in the two tables do not match the function will crash and burn rather than reporting errors in the final elements to be returned. 

Another catch is that it does not accept blank cells as zeros; the 0 must be entered explicitly.  Otherwise you  would need a formula step to test each array, element by element, and return 0 if the test fails (the element value otherwise).

= MMULT(TRANSPOSE(compsPerProduct), IF(ISNUMBER(productQuantity), productQuantity, 0))

 or a 365 version

= LET(
    cleanedVector, IF(ISNUMBER(productQuantity), productQuantity, 0),
    cleanedArray,  IF(ISNUMBER(compsPerProduct), compsPerProduct, 0),
    MMULT(TRANSPOSE(compsPerProduct), cleanedVector)
  )

[#7]

Thank you very much for your guidance and teaching me some new tricks in Excel!!

My issue ended up being a mistake in entering the range of compsPerProduct, I had missed a row due to the panel freeze and didn't catch it, so it through a failure I assume from not having the same amount of rows when performing the function. I went ahead and added the ISNUMBER check into the system because it is a good practice to have checks when you can.

Again I greatly appreciate the help you have provided.