HomeGoogle DocsSpreadsheet

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

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

In Google Sheets, the IMPORTRANGE formula can connect two sheets and import data between them. The QUERY function is another powerful tool in Google Sheets. By using QUERY with IMPORTRANGE, you can import a range of data with specific criteria, such as filtering for values that are less than or greater than a specified threshold.

First, I will explain how to apply the less than (“<“) or greater than (“>”) comparison operators in a column containing dates. Then, we will move on to another example using a numeric field.

Import a Range with Less Than or Greater Than Conditions

Example 1: Import Data Range with Less Than/Greater Than in Date Field

For this example, I have a two-column table in one Google Sheets file, with the range A1:B. The first column contains sales dates, and the second contains sales quantities. The tab name is “Sheet1.”

DateQuantities
1/12/201750
5/12/2017100
10/12/2017150
10/12/2017200
15/12/201750
20/12/201710
21/12/20171000

Steps:

  1. Copy the URL of “Sheet1” from the address bar of your browser.
  2. In another Google Sheets file, enter the following IMPORTRANGE formula in cell A1: =IMPORTRANGE("URL", "Sheet1!A1:B").
  3. Replace URL with the copied URL from the first file.
  4. Google Sheets may display #REF!. Hover over the error and click “Allow Access” to import the data.
  5. To filter this imported range with less than or greater than conditions, wrap the formula in a QUERY formula.

Assume you want to filter rows where the dates in the first column fall between 10/12/2017 and 20/12/2017 (inclusive). Enter these dates in two cells, e.g., C1 and C2.

Replace the original formula with the following:

=QUERY(IMPORTRANGE("URL", "Sheet1!A1:B"), "Select * where Col1>=date '"&TEXT(C1,"yyyy-mm-dd")&"' and Col1<=date '"&TEXT(C2,"yyyy-mm-dd")&"'", 1)

Explanation:

When importing a range with less than or greater than conditions, it’s important to understand how to use the simple comparison operators in the QUERY function.

One more thing! The date criteria in cells C1 and C2 are in “dd/mm/yyyy” format, but when comparing dates, use the keyword ‘date’ followed by a string literal in the format “yyyy-MM-dd.” Example: date "2017-12-10" or date'"&TEXT(C1,"yyyy-mm-dd")&"'.

For more details, check out my tutorials:

Example 2: Import Data Range with Less Than/Greater Than in Numeric Field

Let’s filter the same data used in the first example. This time, we will import the sales quantities in column B within a certain threshold.

To import a range with less than or greater than conditions in a numeric field, use numeric literals like this: where Col2 > 0 and Col2 < 100.

If the criteria are in cells C1 and C2, it should be specified as: where Col2 > "&C1&" and Col2 < "&C2&".

This is because numeric literals are specified in decimal notation in the QUERY function.

Example:

=QUERY(IMPORTRANGE("URL", "Sheet1!A1:B"), "Select * where Col2 > "&C1&" and Col2 < "&C2&" ", 1)

Additional Tip

What if you want to aggregate the imported data?

Using the sample data and formula from above, you only need to make one change to the formula:

Replace Select * with Select SUM(Col2) to aggregate the values.

Alternatively, you can use COUNT instead of SUM to count the records that meet the criteria.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Sheets Vs Excel Formula

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...
Sheets Vs Excel Formula

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...
Sheets Vs Excel Formula

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...
Excel Formula

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

9 COMMENTS

  1. Hi Prashanth,

    Thank you very much for the tutorials and for your effort.

    Can I share with you my sheets, of course without personal data? Could you please help me? I have to import data for the blue criteria (Sheet Test 02, Report): Column C 7,8 & 10,11. I can offer payment for your effort, it is very important for me.

    — Links removed by Admin —

    Thank you in advance!

    Anita

    • Hi, Anita,

      Please correct the date entered in cell G2,G3, and G47 (Sheet: Copy of Test 1, Tab: Professional Test). The date format is wrong. There are other issues.

      I am not going into the detail of the solution here as other readers may not find it useful. So I am sending back you the files (I have made copies of the files for editing) via email.

      Best,

  2. Dear Prashanth,

    First of all, thank you very much for this website, you are doing a great job.

    I have the following problem, could you please help me?

    I have 2 separate Spreadsheets, one of them contains a column with “Received date (for the whole year)”, the second one contains columns for each calendar week separately.

    I have to import data from the first file to the other one, and I need a function that counts how many requests I received for each week separately.

    For example, how many requests have I received in week 6 (04/02/2019-10/02/2019)?

    Please 🙂

    Anita

  3. Just a little correction: Formula1 doesn’t work as typed. There are extra spaces within the single quotes enclosing the date to text conversions that throws a parsing error.

    Instead of:
    "&TEXT(B1,"yyyy-mm-dd")&" ' and Col1<=date' "&TEXT(B2,"yyyy-mm-dd")&" ' ",1)
    it should be:
    "&TEXT(B1,"yyyy-mm-dd")&"' and Col1<=date' "&TEXT(B2,"yyyy-mm-dd")&"' ",1)

    Otherwise an excellent set of tutorials. Thank you very much! They've helped me considerably.

    • Sorry that 2nd line should be:
      “&TEXT(B1,”yyyy-mm-dd”)&”‘ and Col1<=date'"&TEXT(B2,"yyyy-mm-dd")&"'",1)
      Another space somehow made it into the formatting. HTML formatting problem on the site instead?

      • Hi, Ken,

        You are right! I myself noticed that in the past.

        I am posting the formulas after a thorough test from my side. But the issues happen when I publish my posts. If I post the formulas as CODE then the spacing issue happens and may often affect the Query formulas.

        If I post the formulas as plain text formats, then the double/single quotes may cause the issue.

        So the solution is to remove the extra space between single Double quotes in Query formulas (if the formula is posted as CODE) else retype the single double quotes.

        Thanks for the time taken to notify me.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.