Forum Discussion
Solved
Matrix lookup and summing of components formula help
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...
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.
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?
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.
