New answers tagged

0

You can use the following query formula: =QUERY(Q2:S11, "select sum(Q) where R matches '(.*green.*)|(.*toy.*)' OR S matches '(.*green.*)|(.*toy.*)' label sum(Q)''") About QUERY


-1

Just something that i found especially if you find that you have done everything correct with your syntax and links. When attempting to import multiple ranges or sheets, it could be throwing this error due to a lack of permissions granted. It is likely that you have link-sharing turned off for the source spreadsheets that you desire to import and query. In ...


1

There isn't a "pretty print" built-in feature for Google Sheets formulas. IMHO opinion the simplest way to improve formula readability are those already described in How can I pretty-print a formula in Google Sheets? and How to build formula in Google Sheets using content of other cells: Insert spaces and breaklines If you edit the formula to introduce ...


1

You can't order a query by month names because that would order them either alphabetically from A - Z or from Z - A. To achieve your ordering from January to December, you first need to create a mapping as follows: 1: January 2: February ... 12: December Then you can order by month number to get the sorting correctly.


1

You can use the following formula in cell AE4 =ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(FILTER(B4:Z,B3:Z3=DATE(2019,12,16))), "select "&TEXTJOIN(",", 1, IF(LEN(A4:A), "avg(Col"&ROW(A4:A)-ROW(A4)+1&")", )))&""), "select Col2")*1) (If this is what you are looking for, I will update with image and more info as well)


1

Assuming row 1 is the headers, what you need is a basic QUERY formula in cell E2: =QUERY(A2:A,"select A, count(A) where A<>'' group by A label count(A) ''") You could also replace the condition where A<>'' with where A is not null =QUERY(A2:A,"select A, count(A) where A is not null group by A label count(A) ''") Learn more about QUERY


1

The SELECT and WHERE keywords should be included only once. Try =QUERY(combine!A1:D,"SELECT * WHERE LOWER(A) LIKE LOWER("""&B1&""") or LOWER(D) LIKE LOWER("""&B1&""")",1)


1

(Assuming your data is in column Z and cells 10-17). Use this formula for A-.375-VG in one tab: =QUERY(Z10:Z17,"SELECT Z where Z like 'A-.375-VG%' ") and for A-.75-VG in the other =QUERY(Z10:Z17,"SELECT Z where Z like 'A-.75-VG%' ") QUERY


Top 50 recent answers are included