google sheets - Making operations with filtered columns of multiple googlesheet tables - Stack Overflow

admin管理员组

文章数量:1406312

I have 2 tables in a google sheet, and I need to complete a third one with the info of the other 2. The 3 tables are:

Products:

Product	Amount
A	10
B	15

I have 2 tables in a google sheet, and I need to complete a third one with the info of the other 2. The 3 tables are:

Products:

Product	Amount
A	10
B	15

CompsByProd:

Product	Component	Amount	Type
A	x	20	T1
A	y	10	T1
B	x	15	T1
B	y	20	T2
C	x	5	T1
C	z	5	T2

And finally the table Components that has the following header:

Component	Amount

The column "component" of the this table should show the distinct components from the table CompsByProd if their corresponding Product is in the table Product and is of type "T1". This was simple enough to do with the following formula: =UNIQUE(FILTER(CompsByProd[Component];ISNUMBER(MATCH(CompsByProd[Product];Products[Product];0));CompsByProd[Type]="T1")) Which gives the values "x" and "y". "z" is excluded because Product C is not on table Products (If there's a better / cleaner way to do that, the feedback is welcomed)

But for the column "Amount" I need to add the values of the amount of each component of table CompsByProd multiplied by the amount of each Product of table Products. In other words, the table need to look like this:

Component	Amount
x	200 (10 x 20)
y	325 (10 x 10 + 15 x 15)

I've tried a couple of things, for example: =ARRAYFORMULA(IFERROR(VLOOKUP(CompsByProd[Product];Products;2;FALSE);0)*CompsByProd[Amount]) This formula gives me the multiplication of the amount of product and the amount of each component. But when I try to use that result in a SUMIF/SUMIFS to apply the conditions that Type=T1 and Component = the corresponding value in each row, I get an error stating that the argument need to be an interval, which I assume it's not.

I've made a public google-sheet Sample Tables with the info above in case it's easier to understand looking at the actual tables.

Any correction or suggestions appreciated as I'm just started learning google sheets.

• google-sheets

Share Improve this question Follow edited Mar 6 at 15:06 Jason Aller 3,6522828 gold badges4141 silver badges3939 bronze badges asked Mar 6 at 2:25 Felipe Ramírez DarvichFelipe Ramírez Darvich 2855 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Try the following formula-

=QUERY(HSTACK(CompsByProd[Component];MAP(CompsByProd[Product];CompsByProd[Amount];LAMBDA(x;y;y*XLOOKUP(x;Products[Product];Products[Amount];0)));CompsByProd[Type]);
"select Col1, sum(Col2) where (Col2>0 and Col3='T1') group by Col1 label sum(Col2) ''")

Output:

Component	Amount
x	200
y	325

You can also try this formula

=LET(products, Products,comps, CompsByProd,filteredComps, FILTER(comps, ISNUMBER(MATCH(INDEX(comps,,1),INDEX(products,,1), 0)), INDEX(comps,,4)="T1"),uniqueComponents, UNIQUE(INDEX(filteredComps,,2)),amounts, MAP(uniqueComponents, LAMBDA(c, SUM(FILTER(INDEX(filteredComps,,3) *XLOOKUP(INDEX(filteredComps,,1), INDEX(products,,1), INDEX(products,,2)), INDEX(filteredComps,,2) = c)))),HSTACK(uniqueComponents, amounts))

Expected Output

Component	Amount
x	200
y	325

本文标签： google sheetsMaking operations with filtered columns of multiple googlesheet tablesStack Overflow