How to use ARRAYFORMULA in query where clause?

Question

In cell C1:C of my table I got 6 rows with ticket id's. I like to search different spreadsheets to search for those ticket id's and calculate the total hours spent on that ticket.

I have it working using the following formula:

=QUERY({IMPORTRANGE("SPREADSHEETID";"B3:B")\ARRAYFORMULA(TO_PURE_NUMBER(IMPORTRANGE("SPREADSHEETID";"F3:F")-IMPORTRANGE("SPREADSHEETID";"E3:E")))};"SELECT SUM(Col2) WHERE Col1 = '"&C1&"' GROUP BY Col1 LABEL SUM(Col2) ''")

In this example, C1 is where the ticket ID can be found.

Now I thought I could just wrap QUERY in a ARRAYFORMULA and use C1:C instead of just C1 but that won't work. Now I could just copy and paste the above formula in every cell but there must be a cleaner way.

ANSWER

I used the following formula to make it work, thanks to Max's answer below.

=QUERY(
{
  IMPORTRANGE("SPREADSHEETID";"B3:B")\
  ARRAYFORMULA(
    TO_PURE_NUMBER(
      IMPORTRANGE("SPREADSHEETID";"F3:F") - IMPORTRANGE("SPREADSHEETID";"E3:E")
    )
  )
};
"
SELECT Col1, SUM(Col2) 
WHERE Col1 = '" & JOIN("' OR Col1 = '";FILTER(C:C; C:C <> "")) & "' 
GROUP BY Col1 
LABEL SUM(Col2) ''
")

Max Makhrov · Accepted Answer · 2017-08-10 06:51:42Z

1

Sample formula is:

=QUERY({A:B},"select * where Col1 = '"&JOIN("' or Col1 = '",FILTER(D2:D,D2:D<>""))&"'")

answered Aug 10, 2017 at 6:51

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user6655984 · Accepted Answer · 2017-08-09 21:28:35Z

1

No, one cannot create an array of query strings and use arrayformula(query(...)) to run them all at once.

Alternative: instead of

SELECT SUM(Col2) WHERE Col1 = '"&C1&"' GROUP BY Col1 LABEL SUM(Col2) ''

use the query

SELECT Col1, SUM(Col2) GROUP BY Col1

elsewhere on the sheet, and then use vlookup to look up the sum for each value of Col1 that you want. vlookup can be used inside of arrayformula like this:

=arrayformula(vlookup(C1:C10, E:F, 2, 0))

looks up each of values in C1..C10 in the column E (exact match required) and returns the corresponding value in column F (2nd column of the searched range).

answered Aug 9, 2017 at 21:28

user6655984

Collectives™ on Stack Overflow

How to use ARRAYFORMULA in query where clause?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related