Import a Range With Less Than or Greater Than in Google Sheets Query

0
311
Import a Range With Less Than or Greater Than in Google Sheets

In Google Sheets the IMPORTRANGE formula can connect two sheets and import data between them. Query is another awesome function in Google Sheets. By using Query with IMPORTRANGE you can apply conditional import range. Here is an example to conditional Importrange in Google Sheets. Below is the step by step tutorial on how to Import a Range with Less Than or Greater Than in Google Sheets with the help of Query.

We can use the Less than or Greater than comparison operators in date as well as numeric fields in Importrange with the help of other functions and mainly Query. We are going to learn this technique here. You may need to refer the below tutorials. So any time come back to here for reference.

1. Learn Query Function in Google Sheets.

2. Google Sheets IMPORTRANGE function.

Import a Range With Less Than or Greater Than in Google Sheets

First I am going to explain you how to apply less than “<” or greater than “>” comparison operators in a column containing dates.

Sample Data in Google Sheet 1:

Source Data to Import Between Dates

From this sample data, let me answer the following.

How to Import a Range from Another Google Sheets Between Dates

In Google Sheet 2 you can use the below Importrange formula to import the entire data as it is.

=importrange(“URL”,”Sheet1!A1:B10″)

Here you just need to replace URL with the original URL of Google Sheet 1. Now with a Query formula that wrapping the Importrange, we can apply conditional Importrange in Google Sheets.

It’s time to scroll back to the top and check the sample data in Column 1. You can see that Column 1 contains certain dates. Here in Google Sheet 2 I’m importing Column 1 from Google Sheet 1 where the date falls between 10/12/2017 and 20/12/2017, both the dates inclusive.

Date Criteria for Query in Importrange

I have the criteria in Cell B1 and B2 in Google Sheet 2. So here is the formula in Google Sheet 2 to import the data from Google Sheet 1.

Formula 1:

=query(importrange(“URL”,”Sheet1!A1:B”),”Select * where Col1>=date’ “&TEXT(B1,”yyyy-mm-dd”)&” ‘ and Col1<=date’ “&TEXT(B2,”yyyy-mm-dd”)&” ‘ “,1)

Explanation:

When you Import a Range With Less Than or Greater Than in Google Sheets, you should know how to use less than “<” or greater than “>” comparison operators in Query. You can follow the above highlighted formula part.

The Date criteria in Cell B1 and B2 is in “dd/mm/yy” format. But with Query we can only use “yyyy-mm-dd” format. In your leisure, you may please follow my below two tutorials for detailed information in this regard.

How to Use Date Criteria in Query Function in Google Sheets

Convert Date to String Using the Long-winded Approach in Google Sheets

How to Import a Range from Another Google Sheets Between Numeric Values

Here also the formula pattern follows the formula 1 above. But this time the “Where” caluse in Query will be as follows. Here the criteria is in C1 and C2.

where Col1>=”&C1&” and Col1<=”&C2&”

How to Sum or Count Data in Another Google Sheets Between Certain Dates

Here I am using the above sample data and formula 1. We only need to make changes to the formula as below.

Formula 2:

=sum(query(importrange(“URL”,”Sheet1!A1:B”),”Select Col2 where Col1>=date'”&TEXT(B1,”yyyy-mm-dd”)&”‘ and Col1<=date'”&TEXT(B2,”yyyy-mm-dd”)&”‘”,1))

Difference with Formula 1:

Here I’ve just wrapped the formula 1 with the SUM function. Additionally in formula 1, it’s Select “*”, here instead, I’ve only imported the column that we want to SUM that is Col2.

Instead of SUM you can use COUNT to count an imported data between dates or numeric values.

That’s all for now. Like to hear your views in comments.

LEAVE A REPLY

Please enter your comment!
Please enter your name here