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(
...
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,...
-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
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 ...
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) ''")
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 ...
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)
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 ...
Top 50 recent answers are included
Related Tags
google-sheets-query × 840google-sheets × 813
formulas × 359
google-sheets-arrayformula × 240
importrange × 89
vlookup × 54
google-sheets-dates × 43
regex × 35
google-apps-script × 32
filter × 29
concatenate × 27
sorting × 21
google-sheets-arrays × 18
google-sheets-filter × 16
google-forms × 15
google-sheets-charts × 12
regexextract × 12
conditional-formatting × 8
google-sheets-timestamp × 8
google-sheets-custom-function × 8
google-apps × 6
google × 6
pivot-table × 6
google-docs × 5
importhtml × 5