I have a spreadsheet where I am trying to create a table dynamically off a sheet with form responses, but I cant quite figure out how to get the result I am seeking and have it only partially working so far.
Below is range I am running the query on, it is a sheet that is mostly populated by a google form. Other orientation dates (Column B) will be added in the future, which is why I need the formula to be "dynamic".
So, I am trying to write a formula that creates a table showing how many people signed up for an orientation, how many cancelled, and then how many actually attended.
So far I have figured out how to get a table that counts how many people signed up, this is my function:
=QUERY(responses,"select B, count(B) where B is not null group by B label B'Location & Date', count(B)'Registrations'",1)
Note: responses refers to a named range of A:E
And here is the result for what I have so far:
To illustrate, here is the result it would look once working as I intend (I just edited the cells to show how I'd like it to work):
Any thoughts or assistance would be appreciated here!
Use COUNTIFS()
to count orientations and then count cancelled and attended percipient differently. Finally stack them horizontally. Try-
=LET(headers,{"Location & Date","Registration","Cancelled","Attended"},
o,UNIQUE(TOCOL(B2:B,1)),
r,INDEX(COUNTIFS(B2:B,o)),
c,INDEX(COUNTIFS(B2:B,o,C2:C,TRUE)),
a,INDEX(COUNTIFS(B2:B,o,D2:D,TRUE)),
VSTACK(headers,HSTACK(o,r,c,a)))