-2

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.

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.

Sheet: https://docs.google.com/spreadsheets/d/17u-vljsetxP6P6HJnDC9nB0yv-7dYQHXwWU04WZF5ik/edit?usp=sharing 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.

Query: =QUERY(A1:G, "select B, D, E where C contains '"&H1&"'") Where H1 is the name of the person to be filtered around.

Array Formula: =ARRAYFORMULA(SUM(IF(H3:H="Checking-in",IF(H4:H="Checking-out",IF(I3:I=I4:I,J4:J-J3:J,J4:J-J3:J+1))))*24) Note: the AND() function was not working with the array formula which is why I have nested a bunch of IF() statements. I know its not the cleanest but it was the only way I could get it to work.

Riley Hull
  • 1
  • 2
  • 1
    Your question can be greatly improved if you add input table and expected output table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Sep 24 '22 at 20:18
  • whats the desired output? – player0 Sep 24 '22 at 20:24
  • @player0 I updated the post, but the goal is rather than displaying and referencing the queried data, the desired formula would just take in the main data and the name of the specified person and get the total time clocked in. This way it fits in a single cell instead of taking up a bunch of space. – Riley Hull Sep 24 '22 at 20:40
  • answer updated.. – player0 Sep 24 '22 at 23:39

1 Answers1

0

try:

=ARRAYFORMULA(SUM(IF("Checking-out"=
 QUERY(A1:G, "select B where C contains '"&H1&"' offset 1", ), IF(
 QUERY(A1:G, "select D where C contains '"&H1&"'", )=
 QUERY(A1:G, "select D where C contains '"&H1&"' offset 1", ), 
 QUERY(A1:G, "select E where C contains '"&H1&"' offset 1", )-
 QUERY(A1:G, "select E where C contains '"&H1&"'", ), 
 QUERY(A1:G, "select E where C contains '"&H1&"' offset 1", )-
 QUERY(A1:G, "select E where C contains '"&H1&"'", )+1)))*24)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124