QUERY Function

2 months ago

To show on column F all people on column D that have a Buddy ID that matches E3 you can use the filter function. On F3 try something like: =filter(D2:D, A2:A=E3)

1 Like

2 months ago

Always use the headers arguments (although it is strictly speaking optional). See if this helps

=query(A1:A10, "Select *", 0)

Also note that query has issues with mixed data types (eg: text and numbers) within the same column.

2 Likes

3 months ago

Hi, check this out, using QUERY and RANK (which is actualy not necessary, as the names wlll always be ranked in order 1 to 9). =QUERY(B4:C14,"SELECT B, SUM(C) WHERE C IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC",0).

Hope this helps !

1 Like

3 months ago

As an alternative solution, you can also try

=query({A2:E; {A2:B, F2:H}}, "Where Col1 <> '' order By Col2", 0)

1 Like

3 months ago

This will group all the unique values in C (the names) and the average all the values in E (the ranking) and return you a list. It will ignore any blank cells and will work also add any new rows added via the form.

The image shows what this formula does.

=QUERY(C:E,"Select C, AVG(E) where C Is not null Group by C",1)

0 Likes

4 months ago

=arrayformula(xlookup(sequence(counta(split(TEXTJOIN(",",1,M9:M11),","))),scan(0,byrow(split(M9:M11,","),lambda(n,counta(n))),lambda(x,y,x+y)),L9:L11,,1,1))

0 Likes

4 months ago

Query example : Have you tried a QUERY formula instead.. this might be an option : =QUERY(IMPORTRANGE("URL","Schedules!E2:M"), "SELECT E WHERE M CONTAINS '"&A1&"'", 0) you can use cell references so that the data that you are looking for is dynamic instead of it being hard coded in the formula...

1 Like