How to Filter Range and Return Specific Columns in Google Sheets

google sheetsgoogle-sheets-queryimportsorting

This is what I have tried just yet:

=QUERY(FILTER(Recherche!A4:O1002), "select Col1, Col15, Col2, Col3, Col4")

based on this format

=QUERY(FILTER(B:D,D:D>=2),"select Col2, Col1")

What I get is the following error:

Error Can query string to parse for Parameter 2 function QUERY:
NO_COLUMN: Col15

Best Answer

The solution:

=QUERY(Recherche!A4:O1002, "select A, O, B, C, D")

Related Solutions

Google Sheets – Group Events by Day, Count and Aggregate Amounts

The error message says that the same column cannot be selected both with and without aggregation. One solution is to move the count function to another column:

=query(A:B, "select toDate(A), count(B), sum(B) group by toDate(A)")

Another solution, which is also applicable when there is no column B, is to duplicate column A before querying: {A:A, A:B} is the array with three columns in which the column A appears twice. One can refer to these columns as Col1, Col2, Col3 in the query string. The following formula returns the desired table:

=query({A:A, A:B}, "select toDate(Col1), count(Col2), sum(Col3) where Col1 is not null group by toDate(Col1) label toDate(Col1) 'date'")

For readability, the same query string with linebreaks:

select toDate(Col1), count(Col2), sum(Col3) 
where Col1 is not null 
group by toDate(Col1) 
label toDate(Col1) 'date'

Google Sheets Query – Specific Values Based on Date Ranges

Grouping

In general, aggregation functions such as SUM or COUNT are used in the presence of GROUP BY: one specifies how to group the rows so that summation or counting is executed within each group. You don't have any GROUP BY. It would make sense to group by E, so that you count each client id once.

Date format

When dates are used in a query, they have to appear like date 'yyyy-MM-dd', for example

where A >= date '2017-05-01' and A <= date '2017-05-21'

Creating such a string from dynamic dates (e.g., 60 days ago, =TODAY()-60 involves conversion to text using text and an annoying amount of concatenation:

"... where A >= date '" & text(today()-90, "yyyy-MM-dd") & "' and ..."

If you go this route, it is advisable to form the query string in its own cell so you can see what it is. query can take the query string by cell reference.

Alternative: filter

It's much easier to use filter for the task you described. Here is the count of unique values in E2:E where A entry is between 30 and 90 days ago:

=countunique(iferror(filter(E2:E, A2:A >= today()-90, A2:A <= today()-30)))

The wrapper iferror is included to suppress "#N/A" that filter returns when there are no matches. (As pointed out by ocWavean).