1

I'm at a loss as to do what I'm wanting.

Sheet Main:

A B
1 TA 30
2 TB 50
3 TC 12

Sheet TA:

A B
1 C .45
2 D .36
3 E .12

Sheet TB:

A B
1 C .44
2 G .03
3 R .15

Sheet TC:

A B
1 C .10
2 D .06
3 Z .09

For each row in the sheet main I want to sum the result of:

 =query(indirect(" "&A1&"!"&"A:B"),
    "select A,B*'main!B1'")

which would result in:

A B
1 C 36.70
2 D 18.72
3 E 3.6
4 G 1.50
5 R 7.50
6 Z 1.08
3
  • it is not clear which cells should be involved and which are the target ones, I recommend that you attach an example file
    – Daniele
    Aug 16, 2022 at 15:53
  • I 've linked the sheet... i'm trying to get something that will fill in the table starting at E7 docs.google.com/spreadsheets/d/e/… Aug 16, 2022 at 18:38
  • This is the link to your file, converted to an editable spreadsheet
    – Daniele
    Aug 16, 2022 at 20:28

1 Answer 1

0
=QUERY({
{QUERY('Table A'!A:B,"SELECT A,B * " &B5  & " WHERE A IS NOT NULL LABEL B * " &B5  & "''")};
{QUERY('Table B'!A:B,"SELECT A,B * " &B6  & " WHERE A IS NOT NULL LABEL B * " &B6  & "''")};
{QUERY('Table C'!A:B,"SELECT A,B * " &B7  & " WHERE A IS NOT NULL LABEL B * " &B7  & "''")}
},"SELECT Col1, SUM(Col2) GROUP BY Col1 LABEL Col1 'Results', SUM(Col2) ''")

Explanation

We have three subqueries here that we stitched together using an array. Each subquery is simply multiplying the values in the B column to the specified value in the main sheet. I'm not sure if it's possible to do all this in one big query.

WHERE A IS NOT NULL is simply there to remove the whitespaces, and the LABEL B is to remove the header that comes with using an arithmetic operation.

All of these subqueries are stacked one on top of each other using the array. From here, we another query to add up the second column (here in the query is called Col2), and group it all together.

We label it to make it all pretty by removing the autogenerated header and adding 'Results'.

2
  • thanks, i guess i should have pointed it out that there are more than the three sheets and it may be that they are added to and deleted at any time (and deleted the entry from the main sheet also). is there any way to do this without having to repeat the query command with each entry? thanks Aug 21, 2022 at 11:46
  • I don't think that's feasible with normal functions. You'll need to implement an App Script solution. An immediate thought would generate a function with all the queries, and insert it into a cell. Though that seems slow, I better idea might be to restructure your data. Add a third column which would point to which table they belong to.
    – David Tan
    Aug 22, 2022 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.