QUERY Function

Relevant answer

Verhulst Van Neste 1 year 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

Relevant answer

Marc Bergiste 1 year 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).

https://docs.google.com/spreadsheets/d/1NYBfHD2nJGi7PMkaNhlp04S6sBgTtMF-INOoXCQ0xoc/copy?usp=sharing

Hope this helps !

1 Like

Relevant answer

Verhulst Van Neste 1 year ago

As an alternative solution, you can also try

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

1 Like

Relevant answer

Justin Ó Macháin 1 year 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

Relevant answer

Juninawan 1 year 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

Relevant answer

GrandLuLu Paname 1 year 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

Relevant answer

Steven A Fatone 1 year ago

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.

0 Likes

Hot network questions