I have a Google Sheets workbook with 2 worksheets.
Sheet 1 lists about 1000 items (components). The list could grow.
(ignore E through K columns in the above example)
Sheet 2 lists some products (200 or so at the moment, but the list could grow). Each product could contain up to 12 components. These are listed in columns C through N (all of these columns have data validation so that these need to be selected from Sheet 1's list of 1000 components). Column R contains the number of units of the product.
On sheet 1, next to each component, I want to calculate the number of units of the component used across all products. Of course countif will count only the instances of the particular component in Sheet 1!C2:N200. Similarly sumproduct doesn't fit the bill because 1. C through N are not numerical and 2. C through N are not fixed.
Is there a simple way to calculate the number of units for each component? (without using VBScript/Javascript, and without copy-pasting the transpose list of components onto Sheet 2)
I am thinking some quirky way of using sumif or sumifs may be the answer here, but haven't figured out yet.
CodePudding user response：
In Sheet1 cell B2. put this formula:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,QUERY(SPLIT(FLATTEN(Sheet2!C2:N&"|"&Sheet2!R2:R),"|",0,0),"select Col1,SUM(Col2) group by Col1"),2,0)))
if you want to return 0s instead of blanks for unused components, this slight modification should do it.
=ARRAYFORMULA(IF(A2:A="",,IFERROR(VLOOKUP(A2:A,QUERY(SPLIT(FLATTEN(Sheet2!C2:N&"|"&Sheet2!R2:R),"|",0,0),"select Col1,SUM(Col2) group by Col1"),2,0),0)))
CodePudding user response：
A complementary way is to build a data base
=ARRAYFORMULA(SPLIT(FLATTEN(Sheet2!A2:A&"|"&Sheet2!C2:N&"|"&Sheet2!R2:R),"|",,false))
and then compute via a pivot table, added in https://docs.google.com/spreadsheets/d/1Lokoms22tuqU53TlhAcdqaldQe_H7Pq8Jah2kBBYo7c/edit#gid=1233865099