Google-sheets – Dynamic date range query

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).

IMPORTRANGE requires authorization to access an external spreadsheet, but the authorization pop-up only is shown when IMPORTRANGE is not nested inside of other functions.

Once you make the authorization, IMPORTRANGE could be nested inside other functions like QUERY.

On the other hand, your formula includes a cell reference inside the quotes of the SQL statement. Replace "select Col2 WHERE Col1 CONTAINS B1 " by "select Col2 WHERE Col1 CONTAINS " & B1

The final formula should look like this:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dMLGkP32EOoHdI6z17zyTwvn2d4MgbOU-vB2nXrE7Dc/edit#gid=1071458711","SCOREBOARD!B49:B58"),"select Col2 WHERE Col1 CONTAINS " & B1)