title: Putting a Query search inside an array formula in google sheets
body: <p>I am trying to find a way to consolidate a sheets query and an array formula I apply to said query into a single cell. The intent is to avoid having to display the query to apply the array formula, as I have a number of slightly different queries with which to apply the same formula. The main data is a list of clock-in and clock-out times pulled from a google form, the query filters this to only include data about a specific person, and then the array formula computes the total time clocked-in for that person. The end goal would be a single formula which would return the value of the array formula but without having to display and reference the query. In other words, using the main data and the name of the specific person as inputs and getting the total clocked-in time as an output.</p>
  <p>I have tried simply replacing the ranges referenced in the array formula with a query which only returns that column, but it does not seem to be working. I know there is probably a way to do this in scripts, but I don't know how JS. Any assistance would be greatly appreciated.</p>
  <p>Sheet:
  <a href="https://docs.google.com/spreadsheets/d/17u-vljsetxP6P6HJnDC9nB0yv-7dYQHXwWU04WZF5ik/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/17u-vljsetxP6P6HJnDC9nB0yv-7dYQHXwWU04WZF5ik/edit?usp=sharing</a> The main data takes up columns A through G, the query displays the data in H2:J, H1 is the name of the person, and I2 is the array formula.</p>
  <p>Query:
  <code>=QUERY(A1:G, &quot;select B, D, E where C contains '&quot;&amp;H1&amp;&quot;'&quot;)</code> Where <code>H1</code> is the name of the person to be filtered around.</p>
  <p>Array Formula:
  <code>=ARRAYFORMULA(SUM(IF(H3:H=&quot;Checking-in&quot;,IF(H4:H=&quot;Checking-out&quot;,IF(I3:I=I4:I,J4:J-J3:J,J4:J-J3:J+1))))*24)</code>
  Note: the <code>AND()</code> function was not working with the array formula which is why I have nested a bunch of <code>IF()</code> statements. I know its not the cleanest but it was the only way I could get it to work.</p>