New answers tagged google-sheets-query
1
You can get those results with an array formula that massages the data based on whether a row is Debit or Credit, and aggregate the massaged data with a query() that uses a group by clause. To weed out rows with a zero result, and create columns for each of 1% Debit, 2% Debit and 5% Debit separately, wrap the query() in another query() that uses a pivot ...
0
This can be done by first locating the IDs with three errors or more, and then filtering the data by those IDs. Try this:
=sort(
filter(
A2:B,
match(
A2:A,
query(
query(A2:B, "select A, count(B) where A is not null group by A", 0),
"select Col1 where Col2 >= 3",
0
),
0 ...
-1
Instead of 'Query', ArrayFormula with Vlookup will give a better option.
0
The second parameter of the QUERY functions is not using the correct syntax to concatenate values.
Instead of
"SELECT Col1 WHERE Col1 =" A3 "AND Col2 =" B3 ""
if Col1 and Col2 holds only number values use
"SELECT Col1 WHERE Col1 ="& A3 &" AND Col2 ="& B3
if Col1 and Col2 holds only text values ...
2
Place this formula in the top cell (i.e., header cell) of any otherwise empty column:
=ArrayFormula({"Header";IF(B2:B="",,COUNTIF(A2:A,B2:B))})
QUERY is not necessary, as you can see.
You can change the "Header" text to whatever you like.
Another option would be to place the following formula in the top left cell of any two ...
1
This is not an answer per se, but it is a guide that will assist the questioner to develop their script from a limited understanding of apps script coding.
There are two functions in the script:
onOpen - this will allow create a new menu Item called Custom menu. It is largely copied directly from Apps Script Guide on menus
copycheckedboxes - this is the ...
0
You mention two different formats, so chances are that some of the values will be numeric datetime values and some will be text strings that just look like datetimes. The easiest way to cope with this is to convert them all to text strings and extract the time of day part with something like this:
=arrayformula(
countif(
timevalue( regexextract(trim(...
2
You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a number (i.e., to act on two different data types), and QUERY makes no assumptions about what those "should mean" (i.e., it is literal). This is the ...
0
You have 12 sheets for each month of the year. Each sheet has columns for the weeks in that month, each week running from Monday to Sunday. Updating monthly and weekly dates is a time-consuming task. You want to know whether there is a way that the year's start date can be nominated, and the weekly and monthly dates will automatically be updated.
The ...
0
You need to pivot the data before charting it. The pivot formula in the example below is:
=QUERY(A:C,"select A, sum(C) where C is not null group by A pivot B")
You can add another field to use as a filter. For example, if D2 were an email selection, you could use:
=QUERY(A:C,"select A, sum(C) where C is not null"&IF(D2<>"&...
0
The values in Column A below the header (A1) are date values, not text values.
If you want to use Google Query language text operators, among other ways to do get the required results, you might convert the date values to text values by using TO_TEXT Google Sheets built-in function
Instead of
=query(A2:A7,"select count(A) where A starts with '2021-0'&...
1
The query() function happily matches text that contains hyphens.
When you use the contains operator, data in columns of type date gets automatically converted to text in the yyyy-m-d format, lacking leading zeros, regardless of their original format.
You can get what you ask for by converting the dates to text before feeding them to query() like this:
=...
-1
It is NOT a bug.
It works as expected.
The issue is that dates are numbers while starts with and contains look for text.
You can see that in the following example where column B is an exact copy of column A but formatted as text.
Dates are scalar functions. You can read about them here
Also under the where clause it is mentioned
The where clause also ...
4
You can use
=query($A$2:$B,"select sum(B) where A = '"&C2&"' label sum(B) '' ",0)
and drag it down for as many rows as needed.
Please notice how we also need to lock the range $A$2:$B. We would otherwise may get strange results.
As an alternative use a single
=ArrayFormula(IF(LEN(C2:C),SUMIF($A$2:$A,"="&C2:C,$B$2:$B)...
3
Instead of
=query(A2:B5,"select sum(B) where A = '"&C2&"'",0)
use
=query($A$2:$B$5,"select sum(B) where A = '"&C2&"' label sum(B) ''",0)
label sum(B) '' prevents that sum(B) be added as a column header, so now the formula will return a single value which will allow you to drag down your the formula.
$...
0
It appears that Google Sheets does not support the options clause. This is acceptable, based on the documentation reference:
Note that data sources are not required to implement the query
language, or if they do, to implement all features of the language.
Unless you have reason to believe otherwise, you should not depend on
a data source to implement all ...
0
Try this:
=arrayformula(
query(
{ text(A2:A, "yyyy-mm"), B2:B },
"select Col1, Col2
where Col1 is not null
order by Col1 asc",
0
)
)
0
Please try the following
=INDEX(IFERROR(VLOOKUP(D8:D,
QUERY(SPLIT(FLATTEN({Respos!B3:B&"@"&SPLIT(Respos!C3:C,CHAR(10))}),"@"),
"select Col2, Col1 where Col2 <>'' "),2,0)))
(please -as always- adjust formula according to your ranges and locale)
Additional info (following ...
0
How to find closest similar value above current value, and return a different column on the same row
Delete everything from Col D (including the header) and place the following in cell D1:
=ArrayFormula({"Value X"; IF(B2:B="",, IFERROR(VLOOKUP(B2:B&COUNTIFS(B2:B, B2:B, ROW(B2:B),"<="&ROW(B2:B))-1, {B2:B&COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B)), C2:C}, 2, FALSE)))})
A virtual array is ...
Top 50 recent answers are included
