New answers tagged

1 vote
Accepted

Combine an array formula with query function

Use this monster: =arrayformula( query( iferror( value( trim( regexextract( substitute( transpose( split( ...
user avatar
  • 6,503
0 votes

Return value by lookup from multiple value

Try this: =QUERY( 'ID-LookUp'!$A$2:$I$3, "SELECT B WHERE " & REGEXREPLACE( CONCATENATE( ArrayFormula( SPLIT( "B,C,...
user avatar
  • 521
-1 votes

Google Sheets: keep track of days spent in different countries

This is excellent, exactly what I was looking for. I am now trying to adjust it for how many days I'm in each country per financial year 01-Apr to 31-Mar. If you have a moment and could point me in ...
user avatar
  • 1
1 vote
Accepted

Google Sheets: Remove column headers from a QUERY()

To remove headers, you should refer to the data range A2:G rather than the full columns A:G, and explicitly set the headers parameter to zero. Additionally, you need to use the label clause to omit ...
user avatar
  • 6,503
0 votes

Google Sheets: Remove column headers from a QUERY()

try this one: =QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&E11&"'label SUM(E) '', SUM(F) ''")
user avatar
  • 956
1 vote

Google Sheets I need the column sums() of a table produced by a query()

={ QUERY(myData!A:G,"SELECT SUM(E), SUM(F) WHERE D = 'Books'"), {"product";SUM(QUERY(myData!A:G,"SELECT E*F WHERE D = 'Books'"))} } This is simply uses aggregation ...
user avatar
  • 521
0 votes

Can anybody please help me create a Query function to extract data from rows to columns (ignoring zero data) and using a drop-down box

Thank you Adam D. PE Diamond Product Expert for answering on Google forum. QUERY(TRANSPOSE({B1:E1;QUERY(A:E,"select B,C,D,E where A='"&G1&"'",0)}),"where Col2>0&...
user avatar
3 votes
Accepted

Google Sheets Query - Drop down text match

Quote string literals in the query statement, like this: =query(sales!A1:C, "where '" & B2 & "' <= A and A <= '" & B3 & "' ", 1)
user avatar
  • 6,503
4 votes
Accepted

Google Sheets - Group/Aggregate data

You can remove sizes from names with regexextract() in cell F1 like this: =arrayformula( iferror( regexextract(A1:A, "(.+?)-[\dA-Z]"), A1:A ) ) Then query the data: =query(A1:F, "select ...
user avatar
  • 6,503

Top 50 recent answers are included