I have a Google Sheets workbook with 2 worksheets. (I suspect the solution is the same for Excel or Google Sheets, and if need be, I can switch to Excel)
Sheet 1 lists about 1000 items (components). The list could grow.
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)
Sheet 1 lists about 1000 items (components). The list could grow.
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)
