title: How to use ARRAYFORMULA in query where clause?
body: <p>In cell <code>C1:C</code> 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.</p>
  
  <p>I have it working using the following formula:</p>
  
  <pre><code>=QUERY({IMPORTRANGE("SPREADSHEETID";"B3:B")\ARRAYFORMULA(TO_PURE_NUMBER(IMPORTRANGE("SPREADSHEETID";"F3:F")-IMPORTRANGE("SPREADSHEETID";"E3:E")))};"SELECT SUM(Col2) WHERE Col1 = '"&amp;C1&amp;"' GROUP BY Col1 LABEL SUM(Col2) ''")
  </code></pre>
  
  <p><em>In this example, C1 is where the ticket ID can be found.</em></p>
  
  <p>Now I thought I could just wrap <code>QUERY</code> in a <code>ARRAYFORMULA</code> and use <code>C1:C</code> instead of just <code>C1</code> 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.</p>
  
  <p><strong>ANSWER</strong></p>
  
  <p><em>I used the following formula to make it work, thanks to Max's answer below.</em></p>
  
  <pre><code>=QUERY(
  {
    IMPORTRANGE("SPREADSHEETID";"B3:B")\
    ARRAYFORMULA(
      TO_PURE_NUMBER(
        IMPORTRANGE("SPREADSHEETID";"F3:F") - IMPORTRANGE("SPREADSHEETID";"E3:E")
      )
    )
  };
  "
  SELECT Col1, SUM(Col2) 
  WHERE Col1 = '" &amp; JOIN("' OR Col1 = '";FILTER(C:C; C:C &lt;&gt; "")) &amp; "' 
  GROUP BY Col1 
  LABEL SUM(Col2) ''
  ")
  </code></pre>