Pass in a range of cells as variable values to a single query?

Question

I have two sheets, sheet1, and sheet2. Column E of sheet2 contains a quantity and column K contains a text string (i.e. a key). Multiple rows in sheet2 contain the same text string with different quantities.

On sheet1 column B, I have a list of text strings. For each text string, I want to query sheet2 and sum up all the quantities in its E column where the value in the K column matches the text string.

Currently, I need x queries where x is the number of text strings in column B of sheet1.

Each current query in sheet1 looks like this:

Query in sheet1 cell A1 using text string in B1 of sheet1

 =SUM(
  QUERY(
    'sheet2'!$A$2:INDIRECT(CONCATENATE("'sheet2'!O",'sheet2'!P1)), "select  E where K="""&B1&""""
  ))

Query in A2 using text string in B2 of sheet1

 =SUM(
  QUERY(
    'sheet2'!$A$2:INDIRECT(CONCATENATE("'sheet2'!O",'sheet2'!P1)), "select  E where K="""&B2&""""
  ))

and so on...

Cell P1 of sheet2 contains the number of non-zero rows in sheet2

This works well, but the problem is that sheet2 contains 200,000 rows. I have around 100 text strings to search for, so I am currently using 100 queries, each of which looks at 200K rows x 15 columns = 3 Million cells

Understandably it takes a long time for the sheet to update as all these queries re-run when sheet2 is updated.

Is it possible to consolidate all of these queries to one? In other words, tell the formula "I want you to perform the sum-query using each cell in the range B1:B100 as the variable x for K=x in the query and place the results in cells A1:A100 respectively"?

player0 player0 · Accepted Answer · 2019-11-09T09:08:57

in some cases putting it into array may boost a bit the performance:

={SUM(QUERY('sheet2'!$A$2:INDIRECT(CONCATENATE("'sheet2'!O", 'sheet2'!P1)), 
      "select E where K="""&B1&""""));
  SUM(QUERY('sheet2'!$A$2:INDIRECT(CONCATENATE("'sheet2'!O", 'sheet2'!P1)), 
      "select E where K="""&B2&""""))}

however, too many QUERY functions should be avoided to prevent the halt. the best performance belongs to DGET and alternatives include FILTER and SUMIF

Pass in a range of cells as variable values to a single query?

2 Answers