New answers tagged google-sheets-query
1
vote
Accepted
Combine an array formula with query function
Use this monster:
=arrayformula(
query(
iferror(
value(
trim(
regexextract(
substitute(
transpose(
split(
...
- 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,...
- 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 ...
- 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 ...
- 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) ''")
- 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 ...
- 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&...
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)
- 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 ...
- 6,503
Top 50 recent answers are included