# Google Sheets Query function: The Most Powerful Function in Google Sheets

Have you heard of the Google Sheets Query function?

It allows you to use powerful database code (a pseudo-SQL, Structured Query Language, the code used to communicate with databases) to manipulate your data in Google Sheets and it’s SUPER powerful.

It’s arguably the most powerful function in Google Sheets.

This video is lesson 14 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge.

1. If you want to follow along and have the same data and see all of the formula solutions, click this link to make your own copy of the Google Sheet:

Grab the data and solution file for this tutorial:

This is what our starting data looks like:

2. Ensure you have the whole table selected (Pro-tip: click somewhere in your table and hit Ctrl + A (on PC) or Cmd + A (on Mac) to highlight the whole table):

3. Go to the menu: Data > Named ranges… and click this menu. A new pane will show in the right side of your spreadsheet as follows:

4. In the first input box, enter the word “countries”, as shown in the image below. This names our table of data so we can refer to it easily.

5. In Google sheets we use the Google Sheets QUERY function and write our pseudo-SQL code inside this function. So, we’ll enter all of our SQL code inside a QUERY function in cell G1.

`=QUERY(countries,"our SQL code goes here between the quotes",1)`

Ok, now we’re set up, let’s start writing SQL code!

## SELECT all the data with the Google Sheets QUERY function

The SQL code `SELECT *` retrieves all of the columns from our data table.

6. To the right side of the table, type the following Google Sheets QUERY function into cell G1:

`=QUERY(countries,"SELECT *",1)`

7. The output from this query is our full table again, because `SELECT *` retrieves all of the columns from the countries table:

Wow, there you go! You’ve written your first piece of SQL code! Pat yourself on the back.

The equivalent real world SQL code would be:

```SELECT * FROM countries```

Spot the difference: We don’t include a `FROM` clause with the Google Sheets QUERY function. This is different to SQL in the real world, where we must specify a `FROM` clause to select our table.

## SELECT specific columns only

8. What if we don’t want to select every column, but only certain ones? Modify your Google Sheets QUERY function to read:

`=QUERY(countries,"SELECT B, D",1)`

9. This time we’ve selected only columns B and D, so our output will look like this:

Equivalent real world SQL code:

```SELECT country, population FROM countries```

Grab the data and solution file for this tutorial:

## WHERE clause

The WHERE clause specifies a condition that must be satisfied. It filters our data. It comes after the SELECT clause.

10. Modify your Google Sheets QUERY function in cell G2 to select only countries that have a population greater than 100 million:

`=QUERY(countries,"SELECT B, D WHERE D > 100000000",1)`

11. Our output table is:

Equivalent real world SQL code:

```SELECT country, population FROM countries WHERE population > 100000000```

12. Let’s see another WHERE clause example, this time selecting only European countries. Modify your formula to:

`=QUERY(countries,"SELECT B, C, D WHERE C = 'Europe' ",1)`

13. Now the output table is:

Equivalent real world SQL code:

```SELECT country, continent, population FROM countries WHERE continent = 'Europe'```

## ORDER BY clause

The ORDER BY clause sorts our data. We can specify column(s) and direction (ascending or descending). It comes after the SELECT and WHERE clauses.

14. Let’s sort our data by population from smallest to largest. Modify your formula to add the following ORDER BY clause, specifying an ascending direction with ASC:

`=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)`

15. The output table:

Equivalent real world SQL code:

```SELECT country, continent, population FROM countries ORDER BY population ASC```

16. Modify your formula in cell G1 to sort the data by country in descending order, Z – A:

`=QUERY(countries,"SELECT B, C, D ORDER BY B DESC",1)`

17. Output table:

Equivalent real world SQL code:

```SELECT country, continent, population FROM countries ORDER BY country DESC```

## LIMIT clause

The LIMIT clause restricts the number of results returned. It comes after the SELECT, WHERE and ORDER BY clauses.

18. Let’s add a LIMIT clause to our formula in G1 and return only 10 results:

`=QUERY(countries,"SELECT B, C, D ORDER BY D ASC LIMIT 10",1)`

19. This now returns only 10 results from our data:

Equivalent real world SQL code:

```SELECT country, continent, population FROM countries ORDER BY population ASC LIMIT 10```

## Arithmetic functions

We can perform standard math operations on numeric columns.

So let’s figure out what percentage of the total world population (7.16 billion) each country accounts for.

20. We’re going to divide the population column by the total (7,162,119,434) and multiply by 100 to calculate percentages. So, modify our formula to read:

`=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100",1)`

I’ve divided the values in column D by the total population (inside the parentheses), then multiplied by 100 to get a percentage.

21. The output table this time is:

Note – I’ve applied formatting to the output column in Google Sheets to only show 2 decimal places.

Equivalent real world SQL code:

```SELECT country, continent, (population / 7162119434) * 100 FROM countries```

## LABEL clause:

22. That heading for the arithmetic column is pretty ugly right? Well, we can rename it using the LABEL clause (however, careful as this is not part of SQL syntax). Try this out:

`=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage'",1)`

`=QUERY(countries,"SELECT B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Percentage' ",1)`

Equivalent real world SQL code:

```SELECT country, continent, (population / 7162119434) * 100 AS Percentage FROM countries```

Grab the data and solution file for this tutorial:

## Aggregation functions

We can use other functions in our calculations, for example min, max and average.

23. To calculate the min, max and average populations in your country dataset, use aggregate functions in your query as follows:

`=QUERY(countries,"SELECT max(D), min(D), avg(D)",1)`

24. The output returns three values – the max, min and average populations of the dataset, as follows:

Equivalent real world SQL code:

```SELECT max(population), min(population), avg(population) FROM countries```

## GROUP BY clause

Ok, take a deep breath. This is the most challenging concept to understand. However, if you’ve ever used pivot tables in Google Sheets (or Excel) then you should be fine with this.

The GROUP BY clause is used with aggregate functions to summarize data into groups, in the same way a pivot table does.

25. Let’s summarize by continent and count out how many countries per continent. Change your query formula to include a GROUP BY clause and use the COUNT aggregate function to count how many countries, as follows:

`=QUERY(countries,"SELECT C, count(B) GROUP BY C",1)`

Note, every column in the SELECT clause (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY clause (e.g. column C in this case).

26. The output for this query is:

Equivalent real world SQL code:

```SELECT continent, count(country) FROM countries GROUP BY continent```

27. Let’s see a more complex example, incorporating many different types of clause. Modify the formula in G1 to read:

`=QUERY(countries,"SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)`

This may be easier to read broken out onto multiple lines:

```=QUERY(countries, "SELECT C, count(B), min(D), max(D), avg(D) GROUP BY C ORDER BY avg(D) DESC LIMIT 3",1)```

This summarizes our data for each continent, sorts by highest to lowest average population and finally limits the results to just the top 3.

The code is very similar to real SQL and it’s looking pretty complex now! Good job!

28. The output of this query is:

Equivalent real world SQL code:

```SELECT continent, count(country), min(population), max(population), avg(population) FROM countries GROUP BY continent ORDER BY avg(population) DESC LIMIT 3```

Well, that’s all for this tutorial folks!

I hope this tutorial allowed you to understand and write some basic SQL code, and see that it needn’t be intimidating.

And I hope you feel inspired to keep learning!

Please feel free to leave comments or questions below.

This video is lesson 15 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge.

> How to add a total row to your Query formulas

> How to use dates as filters in your Query formulas

## Resources

My free Advanced Formulas course for Google Sheets: Learn 30 Advanced Formulas in 30 Days

Official Google documentation for the QUERY() function.

Official documentation for Google’s Visualization API Query Language.

Want to keep learning SQL? Try this online tutorial from W3 Schools.

## 186 thoughts on “Google Sheets Query function: The Most Powerful Function in Google Sheets”

1. Ben says:

Thanks!

1. Mustho says:

You have to insert the value of the feild, not the name. This is how to do it:

=query(FirstEntry!A2:Y, “Select * Where K ='” & Dashboard!W2 & “‘”)

Note the single quotes, one after the equal sign and another between double quotes just before the closing bracket.

1. Alex says:

Is it possible to use column titles instead of the letters in the query? As in, SELECT MAX(Population)

1. Ben says:

Hi Alex – no, unfortunately you can’t use the column titles inside the QUERY function in Google sheets (see this Stack Overflow thread for some discussion on this subject). The only other variation I’ve seen is the ability to use “Col1”, “Col2”, “Col3” etc. when you combine the QUERY function with one of the IMPORT functions, e.g. try this:

`=query(importhtml("https://en.wikipedia.org/wiki/The_Simpsons","table",3),"select Col1, Col4, Col8 where Col8 > 25",1)`

For details on this, check out this introduction on Stack Overflow.

1. Alex says:

Thank you, Ben!

`importhtml` is another useful function I didn’t know about

2. Hi Ben and Alex,

I had the same question and I think there is a solution for it, you just need to combine the query() function with substitute & match:

`=query(A17:E20, "Select "&SUBSTITUTE(ADDRESS(1,MATCH("Status",\$17:\$17,0),4),"1","")&" ")`

Basically this is combining query() with the SUBSTITUTE() function to turn a column number into a letter, and then we’re using MATCH() to look for the column number with the header name we need (in my example ‘Status’), in the range of the headers (my example \$17:\$17). And that’s it!

1. Ben says:

Nice solution Johannes! Thanks for sharing here!

Cheers,
Ben

2. Susmeet Jain says:

Thank you very much for taking the time to create such quality content!!

3. Jonathan says:

This was really useful, thank you.
I’m struggling to use arithmetic operators in a query. I want to minus the sum of one column from the sum of another. My instinct says
query(data, “select D, (Sum(E) – Sum(G)) group by D where E>G”,1) but that doesn’t work :/
Any guidance would be gratefully received!

1. Ben says:

Hey Jonathan,

You were really close but the WHERE clause needs to come before the GROUP BY, as the WHERE is applied to each row. In SQL there is a HAVING clause that comes after the GROUP BY but it’s not available in this Google Visualization API Query Language. So, the formula would be e.g.:

`=query(data,"select C, sum(D), sum(E), sum(D) - sum(E) where D>E group by C")`

Have a look at Step 3 in this sheet: https://docs.google.com/spreadsheets/d/1YGaB2odHHDVjFSUv-KY10iplxfFaQ4Wxs4dbBYj1ng0/edit?usp=sharing

Feel free to make your own copy (File > Make a copy…) so you can edit it.

Cheers,
Ben

1. Jonathan says:

Great!
I’m getting all fancy now, then I do something that breaks it and I have to decipher the error codes (I’m actually beginning to understand those now too!)
Self-taught, no experience, so thanks again. Your article and reply have helped me improve.
The latest iteration after your input, look at me doing all SQL stuff!
=query(data,”select D, Sum(E) – SUM(G) where E>G group by D order by sum(E) – Sum(G) desc label Sum(E) – Sum(G) ‘Difference'”,1)

1. Ben says:

Awesome! Great work Jonathan 🙂

2. Juwan says:

I think the order of your syntax might be off. Try putting group by after the where clause 🙂

4. David Holcomb says:

Great summary. Thanks much. I am trying to do a query to return duplicate results, and I seem to be doing something wrong with the syntax. It looks like this:

Select I, count(F)
WHERE count(F) > 1
GROUP BY I
Order by count(F)

The error I get looks like this:

Error
Unable to parse query string for Function QUERY parameter 2: CANNOT_BE_IN_WHERE: COUNT(`F`)

Any idea what I am doing wrong here?

1. Ben says:

Hi David,

The problem here is that you’re not allowed aggregate functions (in this case COUNT) in the WHERE clause. The WHERE clause filters data at the row-level and won’t work with aggregated data. In real-world SQL, there’s a filter for aggregate groups called HAVING that comes after the GROUP BY, but sadly it’s not available in Google Sheets Query language.

So the solution here is to remove the WHERE line altogether and then wrap your query inside of another one, which will filter on the groups greater than 1. It’ll look something like this:

`=query(query(,"select I, count(F) group by I order by count(F)"),"select * where Col2 > 1")`

You may need to tweak this to get it to work in your particular scenario.

Here’s a screenshot of a quick example:

Thanks,
Ben

5. David Holcomb says:

Awesome. I was wondering what to do without the HAVING clause. Query a sub-query. Good thinking. And thanks for the Col2 syntax. Don’t know where I’d have found that. After a little tweaking to reach my goals, I achieved this, which works like a charm:

=query(query(‘Form Responses 1’!F:I,”
select I, count(F)
where F = ‘Yes’
group by I
order by count(F)
Label count(F) ‘Instances’
“),”
select *
where Col2> 1
“)

1. Ben says:

Great stuff! 🙂

1. REn says:

I can’t seem to make this work with ArrayFormula

6. Robert says:

Is there anyway to use OR:

iferror(query(importrange(AM3,”my_movies!C6:DF”),”select Col1 where ‘Col107’ OR ‘Col108’ = ‘george'”)))

Any help would be awesome

1. Ben says:

Hi Robert,

If you change:

`”select Col1 where ‘Col107’ OR ‘Col108’ = ‘george'”`

to:

`”select Col1 where Col107 = 'george' OR Col108 = 'george'”`

(i.e. remove the quotes around Col107 and Col108 and add the = ‘george’ for both) then this should work for you.

Thanks,
Ben

7. Hi Ben,

I’m a TC (top contributor) on the Google Sheet forum and this post is probably the best intro to Query that I’ve seen.

I’ve developed fairly extensive sheets and sheet systems for clients and friends, but have remained shy about Queries – the syntax has always seemed too delicate and I just spend too much time in trial and error… “maybe THIS clause goes first, maybe I need a comma here or there” etc.

I’ll be coming back here to review your tutorial often, as I think it contains the answers I need.

Thanks for the post!
Best,
Lance

1. Ben says:

Thanks Lance!

8. Jonathan Leonardo Martinez says:

may I use join?

1. Ben says:

No, SQL-style joins are not possible in the QUERY function. I’d suggest running two separate QUERY functions to get the data you want and then “joining” in the spreadsheet sense with VLOOKUP or INDEX/MATCH.

Cheers,
Ben

9. Murray says:

So, if we can’t use Labels in the =QUERY function, does that mean we are limited to only the first 26 columns? After Z are we stuck?

1. Ben says:

No, you can keep using letters in the same way as columns are labeled in the spreadsheet. So AA, AB, AC etc. becomes your next set of headings…

10. John says:

Excellent tutorial! Many thanks.

I have a question.
I have 2 tabs in a spreadsheet.
As a simplified example of my problem:
Tab 1 has columns: country, population, inflation
Tab 2 has columns: country, area
I cannot combine the information all onto one tab.

How do I create a query which will give me the equivalent of:
select area, population
from Tab1, Tab2
where Tab1.country = Tab2.country

Any advice hugely appreciated.

1. Ben says:

Hi John,

Unfortunately you can’t perform “joins” (bringing data from two different tables together) with the QUERY function, so you won’t be able to do what you’re trying to do with the QUERY function.

So it sounds like a scenario for using a lookup formula (e.g. VLOOKUP or INDEX/MATCH) against the country name and retrieving the area and population into a single tab that way.

Thanks,
Ben

11. Rajnish Chandra says:

I have the following data-set. I just want to have data from it for the financial year 2015-2016 only. How to do it? Please suggest. Thanks.
Col A Col B
2015 1
2015 2
2015 3
2015 4
2015 5
2015 6
2015 7
2015 8
2015 9
2015 10
2015 11
2015 12
2016 1
2016 2
2016 3
2016 4
2016 5
2016 6
2016 10
2016 11
2016 12
2017 1
2017 2
2017 3
2017 4
2017 5
2017 6
2017 7

1. Ben says:

Hi Rajnish,

You should be able to do with this QUERY function:

`=QUERY(A1:B31,"select * where A = 2015 or A = 2016")`

Thanks,
Ben

12. Kenny says:

Thank you for the tutorial! It has helped me a lot these past couple of days.

I was wondering if it was possible to format the results of the query as they are returned. For example, using your sample spreadsheet:

=QUERY(countries,”SELECT B, C”,1)

And instead of Asia, Europe, Africa, North America, South America, we would get AS, EU, NA, SA.

Thank you.

1. Ben says:

Hi Kenny,

The short answer is not really. The easiest solution is to create a small lookup table of the continents and their 2-letter codes and then just add a new lookup column next to the QUERY output columns.

However, it is possible to create a QUERY formula version for this specific example. It works by taking the first two letters of the continent as shorthand, or for the two word continents (e.g. North America) taking the first letter of each word (i.e. NA).

Here’s the full formula (warning, it’s a beast!):

```=ArrayFormula({query(countries,"select B",1),if(if(isnumber(SEARCH(" ",QUERY(countries,"SELECT UPPER(C)",1) )),2,1)=2,left(QUERY(countries,"SELECT UPPER(C)",1),1)&mid(QUERY(countries,"SELECT UPPER(C)",1),search(" ",QUERY(countries,"SELECT UPPER(C)",1))+1,1),left(QUERY(countries,"SELECT UPPER(C)",1),2))})```

which gives an output like this:

Whew, that was a sporting challenge! Here’s a working version in a Google Sheet.

Cheers,
Ben

1. Kenny says:

Wow, that’s exactly what I was looking for! Thank you so much! Looking at the query, I don’t feel so bad about not being able to figure this out myself.

Now to sit here for the next few hours/days trying to understand how this thing works.

Again, thank you and Happy Holidays!

1. Ben says:

Great, and thanks for the challenge 🙂 Happy Holidays to you too!

2. Ben says:

P.S. I’ve added my workings to the Google Sheet, which should help explain the array formula…

1. Kenny says:

I’ve got to tell you that the extras you put into the sheet helped tremendously in helping me understand what you did. Thank you again!

1. Ben says:

Great! That’s the key to all of these larger formulas/problems: break them down into manageable chunks…

Hi Ben,
How can we show top 10 populous cities in each continents??

13. Jonathan says:

hi, how in the sql statement do a reference for a cell using where
example,=query(libro;”SELECT C WHERE B=’A1′”;1). a1 doesn’t work. a1 has a id

1. Ben says:

Hey Jonathan,

You’ll need to move the A1 cell reference outside of the quotes to get this to work. Something like this:

`=query(libro,"select C where B = " & A1,1)`

Hope that helps!

Cheers,
Ben

1. Jonathan says:

It works when it’s a number but doesn’t work when it’s a text. May you help me and tell also how could I use wildcards. Thank’s

1. David Holcomb says:

Try this:
=query(libro,”select C where B = ‘” & A1 & “‘”,1)

14. Great article. But how do you perform subqueries? Like:

=query(libro,”select A, (select Count(B) where B < 4)

?

1. Ben says:

Great question. You can nest the QUERY functions to get a subquery type of behaviour. So use the first subquery to create a subset of your data, then insert this as your data table in the second query:

`=QUERY(QUERY(yourData,"your select"),"your 2nd select")`

Hope that helps!

Ben

15. Ben,
As always, excellent work! Thank you for putting together such well documented tutorials. I will often add your name to my google searches to get results from you, specifically.

I was hoping QUERY could be the solution for a particular challenge I have now. The problem is best illustrated here: http://imgur.com/a/Kbkm1

I’m trying to write a formula that will count the items in one column that also appear in a separate column. For other, similar situations, I’ve added a helper column as seen in column D and the formula bar in the screenshot. I would then sum that entire column. I’m trying to find a way to do this without the helper column—putting all of this into one cell, such as cells E3 and E6, in the example (whose desired results would be 3 and 2, respectively).

Is there some sort of query (or other function) that would provide this? Perhaps SELECT…COUNT…WHERE…(IN?) Or does QUERY only work with data structured as a database, where each row is a record and linked all the way across?

An alternative would be to build a custom formula with GAS. That would be a fun project, but if there’s something easier, that would be nice.

A secondary goal for this project (in a separate formula) is to return those values that were repeated in other columns, e.g., E3 (and overflow) would be:
monkey
pen
magic

(if you want the sample data to experiment with, you can find it here: https://goo.gl/e1acwu. Hopefully will save typing out some dummy data.)

If you have a chance to take a crack at this, I’d be interested to see what you find. Thanks again for your work!

1. Ben says:

Hey Andy,

This is a perfect problem to solve with array formulas.

So these ones give you the counts:

`=ArrayFormula(count(iferror(match(C2:C1000,A2:A1000,0),"")))`

`=ArrayFormula(count(iferror(match(C2:C1000,B2:B1000,0),"")))`

and this one will output the matching values:

`=ArrayFormula(query(sort(vlookup(C2:C1000,A2:A1000,1,FALSE),1,TRUE),"select * where Col1 <> '#N/A'"))`

Hope this helps!

Cheers,
Ben

1. Ben, thank you! This is awesome. I definitely need to explore array formulas more. I have a feeling knowing their uses could open up a lot of possibilities (or simplifications, at least).

1. Ben says:

Yes, they’re awesome and super handy when you get the hang of them, although they can be much slower than regular formulas if you have big datasets.

16. Augustiine Agbi says:

Great piece

17. Bill Reid says:

My years as a SQL dabase programmer are still useful, I see! Thanks Very Much for showing this, now my Sheet data is truly useful!

18. Goa says:

Thank you Ben, makes my life lot easier with importhtml and query!

1. Goa says:

Hi Ben. How can i use the AND function with query.
e.g I want all states in columnA which are >5 from column B AND >10 from column C.

1. Ben says:

Hey Goa,

Not sure what your data range looks like, but you can simply have multiple WHERE clauses, like so:

`=QUERY(A1:E100, "select A,B,C where B > 5 and C > 10")`

Cheers,
Ben

19. Abdul Faruque says:

No INSERT option. Any workaround?

1. Ben says:

The QUERY function can’t modify the underlying data, it can only output a copy of the data after doing any filter/aggregation etc. to it. So it’s only like SQL in the querying your data sense, not in a database admin sense.

20. Hi Ben,
I have been using query for quite sometime now and I have recently encountered an issues that i could use your expertise. So I use d the query function to pull data from multiple tabs in the same worksheet. The data in these tabs change everyday but the problem arises when one of these tabs do not have any data. I have tried both ” where Col1 ‘ ‘ ” and “where Col1 is not null” not avoid the empty tabs but it doesnt seem to work.

The query function is as follows:
=QUERY({‘ BD CR Control’!\$A\$16:\$J;’ BB CR Control’!\$A\$16:\$J;’ CD CR Control’!\$A\$16:\$J;’ DB CR Control’!\$A\$16:\$J;’ GB CR Control’!\$A16:\$J;’ RB CR Control’!\$A\$16:\$J},”select Col4,sum(Col7),sum(Col10),sum(Col9),sum(Col8) where Col1 is not null group by Col4 label Col4 ‘Device’,sum(Col7) ‘Users’,sum(Col10) ‘Reg Visits’,sum(Col9) ‘Regs’,sum(Col8) ‘FTDs’ “)

Sid

1. Ben says:

Hi Sid, what’s the error message say? Can you share an example sheet? I couldn’t replicate this error myself ¯\_(ツ)_/¯

1. David says:

Sid,

I do something similar to this, but in two separate steps. Might be more work than you are interested in, but here’s what I do. I have a worksheet that does the combining, and I use a sort function to surround it all. That way any blank lines from any of the tabs will sort to the bottom. Like:

=sort({‘ BD CR Control’!\$A\$16:\$J;’ BB CR Control’!\$A\$16:\$J;’ CD CR Control’!\$A\$16:\$J;’ DB CR Control’!\$A\$16:\$J;’ GB CR Control’!\$A16:\$J;’ RB CR Control’!\$A\$16:\$J},1)

Then I run the query based on THAT data, in which I can refer to columns by letter rather than Col#.

In case that helps…

-David

1. Ben says:

Thanks for sharing David!

2. Hi David,
I did try this but the issue is that the 2mill cell limit will be reached. This is why I wanted to query directly from multiple tabs rather than querying from the combined tab which doubles the size of the data.

Sid

2. Hi Ben,
Like you said. The error occurs in a weird inconsistent way. But I did come up with a temporary fix by passing a script that populates dummy values to the first row of the empty sheet.

Sid

21. Lucy says:

Hi Ben,

I’m looking for a way to produce a ‘search’ function using the query tool, is this possible? for someone to look up any word or number and only the results in the database containing that word would be displayed. I’ve seen examples of this but unsure where to begin.

Any help is much appreciated,
Thank you!
Lucy

1. Ben says:

Hi Lucy,

Sounds like you could use Data Validation (see here for an example) to create drop down lists from which the user could select what they want, e.g. which category.

Then your query function would look like something like this:

`=QUERY(data,"SELECT A, B, C WHERE C = '" & A1 & "' ",1)`

where the reference A1 is the cell with the data validation in, or the cell where someone would type in whatever they wanted to ‘search’ for. Note this can be any cell, doesn’t have to be in A1.

Hope that helps!

Ben

1. How do you return any rows that contain a certain word in a particular column?

So if we say we are searching for the word “food” would it be something like this:

`=QUERY(data,"SELECT A, B, C WHERE C = '" & food & "' ",1)`

Thanks in advance Ben. I know I’m close but not finding much helpful documentation anywhere else.

1. Ben says:

You’re close! You just need to modify your QUERY function to be, with the word you want to match inside the single quotes:

`=QUERY(data,"SELECT A, B, C WHERE C = 'food'",1)`

Cheers!
Ben

2. Michael Thomas says:

Regarding the Drop-Down, what would be a value that would select “all” the rows in the query instead of specific ones?

Such as,
Apples
Oranges
Bananas
All Fruits

3. Jane says:

I can’t get this to work where A1 is equal to a date. It works great for any other formatted data.

22. anso says:

Hi
great tutorial !
I need some help with the date
=QUERY(Display;”SELECT B,N,C,E,F,I,O WHERE E>today() and N starts with ’36′”;1)
it doesn’t work…

Thanks 🙂

23. Brian Matthews says:

Here is a handy apps script function that extends the ability of query() so that you can specify the columns by header rather than ‘D’ or Col5 or whatever. Much more intuitive

``` /** * Enhances Google Sheets' native "query" method. Allows you to specify column-names instead of using the column letters in the SQL statement (no spaces allowed in identifiers) * * Sample : =query(data!A1:I,SQL("data!A1:I1","SELECT Owner-Name,Owner-Email,Type,Account-Name",false),true) * * Params : rangeName (string) : A quoted form of the range of the headers like "data!A1:I1" not data!A1:I1. * queryString (string) : The SQL-like query using column names instead of column letters or numbers * useColNums (boolean) : false/default = generate "SELECT A, B, C" syntax * true = generate "SELECT Col1, Col2, Col3" syntax */ function SQL(rangeName, queryString, useColNums){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var range = ss.getRange(rangeName); var row = range.getValues()[0]; ```

``` for (var i=0; i<row.length; i++) { if (row[i].length < 1) continue; var re = new RegExp("\\b"+row[i]+"\\b","gm"); if (useColNums) { var columnName="Col"+Math.floor(i+1); queryString = queryString.replace(re,columnName); } else { var columnLetter=range.getCell(1,i+1).getA1Notation().split(/[0-9]/)[0]; queryString = queryString.replace(re,columnLetter); } } //Logger.log(queryString); return queryString; } ```

1. Ben says:

Very nice! Thanks for sharing. 🙂

2. Simon says:

WOW! THANK YOU for sharing! I’ve wanted to do this for so long.

24. Samuel St-Pierre says:

Is there a way to select the Only the Country with the smallest population of each continent ?

1. Ben says:

This was a great question, and much harder than it first appeared. Assuming I have the population dataset in columns A to D, then this formula will give me the countries with the minimum population for each continent:

```=ArrayFormula(   {     query(A1:D234,"select C, min(D) group by C order by min(D)",1),     {       "Country";       vlookup(query(A1:D234,"select min(D) group by C order by min(D) label min(D) ''",1),{D1:D234,B1:B234},2,0)     }   } )```

What a beast! There may be other, more succinct, formulas out there…

I’ll probably turn this into a formula challenge/blog post 🙂

Cheers,
Ben

1. Leszek says:

What if you’d like to have not min/max value, but for example TOP3?

25. Jason says:

Hi Ben

I have a dataset:
Quarter 1 1 1 2 2 2
Month 1 2 3 4 5 6
Item1 8 6 5 9 2 4
Item2 3 1 4 7 3 5
Item1 4 7 2 6 2 4

I need to sum Item1 where quarter = 1.

Will query allow me to do this?

1. Ben says:

Query is part of the answer but won’t get you there on it’s own, because you can’t filter on columns. If you know Quarter 1 was always the first three columns you could do it with a single query, but I’ve taken it to be more general, wherever quarter = 1 in row 1. Assuming your dataset was in the range A1:G5, then this formula gives the sum of Item1’s where Quarter = 1:

`=sum(query(transpose(query(A1:G5,"select * where A = 'Item1'",1)),"select sum(Col2), sum(Col3) where Col1 = '1' group by Col1",1))`

Essentially, it’s just a query to select item 1’s, then that gets transposed, then I run a second query to select Quarter 1’s, then sum the answer!

Cheers,
Ben

1. Jason says:

Had not considered transpose. Thank you!

26. Jeremy says:

So, I’m trying to use Data Validation for user selection to compare certain cells to one another (trying is the keyword). I’m new to a lot of what sheets has to offer and not too knowledgeable, but I can normally fumble my way through it to figure it out. However, I’m trying to get query to output a single cell instead of a row or column from the data set range. Maybe I’m using this in a way it’s not intended to used.

So, in the example table above with the countries, could you set up a Data Validation to have the user select a specific country and have query spit out just the population of that country?

1. Ben says:

Hey Jeremy,

In answer to your specific question, if you have a data validation in one cell that creates a drop-down list of countries then you could just use a VLOOKUP to retrieve the country population from that data table, e.g.

`=vlookup(F2,B:D,3,false)`

where the data table was in columns A:D and the data validation was in cell F2.

If you want a single cell answer back from your query function, then you’ll want to return a single column with an aggregation function.

Hope that helps!

Ben

27. Mahesh says:

Hi Ben, I used this function, but i am not getting the result correctly. I have created the range. it has dates and order number and relative information. and total lines are 20. I used the function as below. =QUERY(Data,”select K where Q >3″,1), all the Q column contains 10(above 3 as per the formula. but it is showing the result of only 18. 2 row result is not appearing.

can you help me figure it out. and am i using this wrong.

Reagards
Mahesh

1. Ben says:

Hi Mahesh,

Hmm, sounds like it maybe an issue with the data range you’ve included in the query, so make sure the full data table is referenced in the query function. The 1 at the end of the query function means you have a header row, so make sure you’ve included that in your query range too.

Also, check that the two missing rows have 10 in them, and that’s the number 10 and not stored as a text value. (To fix, copy in one of the other 10 values.)

Cheers,
Ben

28. Brad Lowry says:

Hi,
Great Tutorial! Thanks.

I had noticed that, in the original dataset, the value for ‘Rank’ was not always an integer, rather an ’emdash’ (I think, whatever). See Puerto Rico.

However, the result for those countries for SELECT * and beyond was empty! I wondered why this was the case and I poked around and had almost given up. Then I stumbled upon this:

Under the collapsible fieldset with the legend:
‘Missing data when you query text and numbers’

This anomaly is explained well.

Regards!

1. Ben says:

Thanks Brad. Good point!

29. Temani says:

Is it possible to select multiple columns and group by multiple columns?

Here’s what I have so far and it works:

`=query(Attendance!A1:D," select B, count(A) where A is not null group by B label count(A) 'Count' ",1)`

But I’d like it to say this, which does not work:

`=query(Attendance!A1:D," select B, C count(A) where A is not null group by B, C label count(A) 'Count' ",1)`

Column A is a time stamp.

I’m looking to select first and last name from google form responses and group by first and last name.

1. Ben says:

Yes, it’s possible. Looks like you’re missing the comma between the C and count(A) after the select 😉

Try:

`=query(Attendance!A1:D," select B, C, count(A) where A is not null group by B, C label count(A) 'Count' ",1)`

Cheers,
Ben

30. Arlen Gregory says:

I have adapted some of this help as well as off another site. I am close but need to clean it up a bit more for my uses.

This is a zillow scraper for realtors.

In column A1 is the zillow URL for a home.

In column B1
=ArrayFormula(QUERY(QUERY(IFERROR(IF({1,1,0},IF({1,0,0},INT((ROW(\$A:A)-1)/20),MOD(ROW(\$A:A)-1,20)),importXML(\$A:\$A,”//span[@class=’snl phone’] | //meta[@property=’og:zillow_fb:address’]/@content | //meta[@property=’product:price:amount’]/@content| //div[@class=’hdp-fact-ataglance-value’] “))),”select min(Col3) where Col3 ” group by Col1 pivot Col2″,0),”offset 1″,0))

The output is 19 columns
address,price, 9 columns of data i dont need, and 8 columns of phone numbers sorted by min value

What I need is

Address, price, 8 phone numbers as pulled from import order 1 2 3 4 5 6 7 8, then the rest of the data in any order.

Because I can search the 10-19 columns for data i need, but sometimes there is no extra data, and the phone numbers get put in different columns and messes up my references.

If I cant get special sorting via querries, can we turn off sorting and just go with 8 phones up front, then price then the rest of the data?

31. Leighana says:

Is it possible to have it feed in the labels also? example:

`=QUERY('Orig Data'!2:1001 , "select sum(H) group by G" )`

G is a column of state abbreviations. this outputs a sum of \$ by state. I am wanting it to also tell me what state (from column G) the sum correlates to, i.e.
AR \$1000
WA \$600
etc.

1. Ben says:

Hey Leighana,

You need to just add column G into your select, before the sum(H), so your formula looks like:

`=QUERY('Orig Data'!2:1001 , "select G, sum(H) group by G" )`

Cheers,
Ben

32. Mahamud Hasan says:

I am trying to move data (entire row) from one sheet to another if column I contains the word ‘Low’ or the column J contains the word ‘Resolved’.

`=QUERY(Current!A10:M10,"select * where A='Low'",1)`
`=QUERY(Current!A10:M10,"select * where A='Resolved'",1)`

But it also copies data that doesn’t contain the word ‘Low’ or ‘Resolved’.

Any help?

1. Ben says:

Hey Mahamud,

Hmm, the range you’ve entered into the query formula is just one row, and the “select *” will select all the columns in that row.

Typically the query formula range is over multiple rows, e.g. A1:M100, and then the WHERE clause can be used to select just rows within that range that fit the criteria.

Hope that helps!

Ben

33. Bry says:

How do we use query formula if our data is from a different sheet? Output is on another sheet.

1. Ben says:

Hey Bry, you’ll want to use the IMPORTRANGE formula to first grab your data from the different sheet, and then put that inside your QUERY function.

You need to run the IMPORTRANGE formula first on its own. It’ll give a #REF! error at first, until you click where it says “You need to connect these Sheets. Allow Access”.

Then you can use it inside your QUERY function.

Hope that helps!

Ben

34. David Holcomb says:

Ben,

I have been trying to solve this “running total with two criteria as of the current entry” challenge for a while. See my sample sheet at: http://bit.ly/2z5ooHp I recently hit upon using a query to solve it (I use queries a LOT. LOVE em).

I first solved my challenge using a SUMIF solution, see column E. But I couldn’t figure out how to make that work in an ARRAYFORMULA. Google searching seems to indicate that this is a losing battle. But it lead me to trying a ARRAYFORMULA(MMULT) solution, which I couldn’t make work either.

So then I tried a totals query as a solution – see column G. Worked like a charm, with one oddity. I had to turn both the Date and the Timestamp into serial numbers for the query to work. I tried every permutation of converting the date into other things within the query criteria I could think of, to no avail. So…
QUESTION 1 : How can I use dates as criteria in queries without having to change them into serial numbers first. I am having this challenge in other sheets with queries referencing dates, and it’s a bit frustrating.

Having made a query work, I tried to wrap it in an ARRAYFORMULA, because ultimately I will be deploying this in a sheet that is fed by a form, so I want the field to auto populate with each new entry. I have played around with it quite a bit, but can’t seem to make it work.
QUESTION 2: Is an ARRAYFORMULA(QUERY()) also a losing battle, or am just missing something in my formula?

1. Ben says:

Hey David,

Re question 1, have a read of this article for how to use dates in QUERY function: https://www.benlcollins.com/spreadsheets/query-dates/

You need to have the keyword “date” and date needs to be in the format YYYY-MM-DD. You can always create this with formulas if needs be, like so:

`=year(A2)&"-"&month(A2)&"-"&day(A2)`

Re question 2, yes, you can use array formulas with QUERY formulas, but I’m not sure that it’s the correct solution here.

The Array + MMULT formula for a running total is:

`=ArrayFormula(if(\$B\$2:\$B\$100,mmult(transpose((row(\$B\$2:\$B\$100)< =transpose(row(\$B\$2:\$B\$100)))*\$B\$2:\$B\$100),sign(\$B\$2:\$B\$100)),IFERROR(1/0)))`

I'll let you know if I find a way to subtotal with conditions... work in progress ;)

Cheers,
Ben

35. Andy says:

Hi Ben,
I’ve really appreciated this tutorial and the formula challenge section that followed! I am actively engaged with a problem and wondering if you might be able to help.

I’m trying to query data on multiple sheets using named ranges, where the list of named ranges is dynamic. A simplified version of the query statement I’m using is: =query({ArrayFormula(indirect({A12:A18}))}, “select *”, 0)

In this example, A12:A18 contains a list of named ranges. In actuality the list of named ranges is produced by a subquery that references a lookup table to assemble the named ranges in the form of “[product type]_[receiving date]”. That query looks like this:
=query(ARRAYFORMULA(if( {B2:F10} = “Clover”, “Clover_” & text({A2:A10},”yyyymmdd”), “”)),”Select Col2 Where Col2 ” “, 0).

The subquery successfully generates a list of the named ranges I want, but the master query will only display data from the first named range. I don’t get any errors, just not the results I’m after. Is INDIRECT() not able to function as an ArrayFormula in the way I’m trying to use it? Is there another way to query a dynamic set of named ranges?

1. Ben says:

Hey Andy,

Can you share a sheet so I can see how this is setup? Sounds like an interesting challenge!

Cheers,
Ben

36. Andy says:

Hey Ben,
Here’s a link to a sheet with the structure of what I’m working with.. https://goo.gl/ZNnqRw.

Aside from the issue of INDIRECT() not functioning as an ArrayFormula, assembling the named ranges from the lookup table had to be a multi-step process because the first step has them spread out across multiple columns according to where they are found in the lookup table. I haven’t found a way to collapse the matrix into a single column without the second step. Can you think of a way to accomplish that in one step?

As for the issue with indirect(), I’m starting to think it just doesn’t iterate as an array formula. Is that correct? I haven’t been able to find any official documentation one way or the other. Starting to look into a script solution to combine the ranges.

By the way, the green and blue shaded areas in the last tab are just two ways of compiling the named ranges from the lookup table. I suspect the one that uses index() will be more amenable to a dynamic lookup table, but haven’t played around with it yet. For now, the lookup table references are static.

Cheers,
Andy

37. Ian Schwartz says:

This was really helpful. Thanks

38. Jeff says:

How do I Concatenate within an Import Range?

I’ve successfully queried a worksheet into the same file with the following formula.

`=QUERY(IMPORTRANGE("URL","Master!B6:V"),"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col12,Col13,Col18,Col20,Col21 Where Col20'08.Installed' and Col20'09.Invoiced'and Col20'10.Closed' and Col20'11.Warranty' and Col10'Material Only' and Col10'Pool Pump' and Col8'Unassigned'")`

The purpose of the query is to return specific columns filtered by specific criterion.

Rather than returning the individually selected Col7, Col8, Col9 I’d actually like to return the concatenated “Col7-Col8-Col9” within the query.

Is this doable?

1. Jeff says:

Thought I would post this solution from Dazin in case anyone found it helpful.

`=QUERY({IMPORTRANGE("URL","Master!B6:G"),ARRAYFORMULA(IMPORTRANGE("URL","Master!H6:H")&"-"&IMPORTRANGE("URL","Master!I6:I")&"-"&IMPORTRANGE("URL","Master!J6:J")),IMPORTRANGE("URL","Master!I6:V")},"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col12,Col13,Col18,Col20,Col21 Where Col20'08.Installed' and Col20'09.Invoiced'and Col20'10.Closed' and Col20'11.Warranty' and Col10'Material Only' and Col10'Pool Pump' and Col8'Unassigned'")`

I didn’t realize the “Select…where” would work despite breaking up the import ranges into groupings but was able to follow Dazin’s formula once I saw it.

1. Ben says:

Interesting! Thanks for sharing the solution 🙂

39. bpf says:

Great overview of Query. I have an “intermediate” level question that I wanted to try out on you.
I discovered that it is possible to use the results of one query as the data range for a second query (similar to using IMPORTDATA as a data source). I wanted to use this to compute a new measure from my data (so, e.g., I have Sales and COGs as rows in a column called “Account” but wanted to compute the difference (gross profit). To get GP, I query the original data and pivot by Account, then use that output as the data for a second query, where I compute GP as the difference between 2 columns from the pivoted data. It all works except when a value is null (which can happen regularly with such pivoted data). When you subtract a null from a number in Sheets, you get a number (null is interpreted as zero). In the calc returned by Query, number minus null is null. Am I missing a trick? Is there a way to make Query treat those nulls as zeroes? Here is a link to a sheet with an example of what I am talking about…Thanks for any insights.

1. David H says:

bpf,
This is a great question. Also nice use of nested queries.
I have been having the null values in a pivoted query problem for other reasons. In excel a pivottable allows the user to specify what should go in the table in the case of a null value. This would be a good place to enter a 0.
But since you (and I) are trying to use the more sophisticated and powerful query() function, it’d be nice to know how to work around this problem – whether that be a function option about which we are not aware, or a useful workaround)

1. bpf says:

Thanks for the feedback David. Looks like we are in the same boat. I added a pivottable to the test file (from Sheets GUI) to see how it treats blanks (the answer is, treat as zero). It will be interesting to see if anyone knows of a way to emulate that within the Query syntax. I will keep hunting around and post here if I find anything.

1. Ben says:

Hey bpf & David,

Good question! Thanks for sharing here. There’s no neat and tidy way to do this as far as I know, but I can share a workaround. Essentially you split the data using query functions into a group of not nulls and a group of nulls, and then set the nulls to 0.

Using the example data in the sheet you shared, I start with this query function to get only the not nulls:

`=query(query(A1:D11,"select B, sum(D) group by B pivot C"),"select Col1, Col2, Col3 where Col3 is not null")`

Separately, this function will get me just the nulls (only null in the sales column) and set the third column to 0:

`=query(query(A1:D11,"select B, sum(D) group by B pivot C"),"select Col1, Col2, 0 where Col3 is null")`

This sets all the values to 0 where there was a null in Col3.

Then I combine these two datasets into a single table, using the `{}` notation:

`={query(query(A1:D11,"select B, sum(D) group by B pivot C"),"select Col1, Col2, Col3 where Col3 is not null");query(query(A1:D11,"select B, sum(D) group by B pivot C"),"select Col1, Col2, 0 where Col3 is null")}`

Then I run a final query over this dataset to do the subtraction calculation to get Gross Profit, sort the data correctly and remove that pesky header row that was introduced by the null query part:

```=query({query(query(A1:D11,"select B, sum(D) group by B pivot C"),"select Col1, Col2, Col3 where Col3 is not null");query(query(A1:D11,"select B, sum(D) group by B pivot C"),"select Col1, Col2, 0 where Col3 is null")}, "select Col1, Col2, Col3, (Col3 - Col2) where Col1 <> 'Market' order by Col1 label (Col3 - Col2) 'GP Calc'")```

So, not particularly elegant, and this answer is specific to the Sales column having a null entry, although it could be modified to handle a null in COGS too.

Here’s a link to a sheet with the formula in yellow: https://docs.google.com/a/benlcollins.com/spreadsheets/d/1ZNClyrua5WnCGYAyWmA4yU3fCn1dT43ULYo8yo4jfIM/edit?usp=sharing

Is it worth it? I’ll let you decide 😉

40. bpf says:

Thank you for addressing my question. I can see the gist of your thinking in the reply but I cannot seem to get into the example sheet to see the working formulas? Can you make the file accessible to me? Thanks again.

1. Ben says:

Ah, sorry, wrong settings. Have shared with you now. Cheers.

41. LL says:

Is it possible to make query something like this:
=QUERY(AllResponses!A:AI;”Select C:I Not D”)?

A query that selects a range of columns without specified columns?

1. Ben says:

Unfortunately, you have to select the columns you want by referencing them specifically…. e.g. `"select C, E, F, G, H, I"`

42. Cody Sun says:

Dear Ben,

I wanna ask if we are able to use COUNT() immediately after SELECT?
Something like =Query(Range,”select count(G) group by E”,1)?

43. Cody Sun says:

Dear Ben,

I wanna ask if we are able to use COUNT() immediately after SELECT?
Something like =Query(Range,”select count(G) group by E”,1)?

**Duplicate reply as I forgot to check the notify me function.

1. Ben says:

Yes, you can do that! Only requirement is that all the columns in your select statement are either aggregated (i.e. have a function like SUM() around them) or are mentioned in the Group By clause.

44. Naveen says:

Hi Ben,

First of all Thank for this awesome Tutorial.

Can you make a query where i put data in col1 and result of query in Col2 then i delete the data of Col1 and put data again and this time the result of Query will be in Col3 instead of Col2 and Col2 data does’t change.
For Example:-
Col1 Col2 Col3
15 15
After delete Col1 data and put new data
Col1 Col2 Col3
20 15 20

Hi Ben,

is there a to way to omit certain columns from a query depending on a value found in the current row?

E.g. if I have the following rows:

1,2,3,’no’,4,5,6
1,2,3,’yes’,4,5,6
1,2,3,’no’,4,5,6

I’d like to have result set that would look like this:

1,2,3,’no’
1,2,3,’yes’,4,5,6
1,2,3,’no’

or optionally like this:

1,2,3,’no’,’-‘,’-‘,’-‘
1,2,3,’yes’,4,5,6
1,2,3,’no’,’-‘,’-‘,’-‘

In other words, whenever the fourth column contains the word ‘no’ do not return (or optionally return a detault value for) the remaining columns in the row.

Any hint would be very much appreciated.

Thanks,

1. Ben says:

I think the only way to do this is two separate queries. Imagine I have data like yours in the range `A1:F5`, with the Yes/No column as column D.

The first one would have a `WHERE column = 'yes'` clause and return those rows of data. The second query would look something like this (to get the blanks): `=query(\$A\$1:\$F\$5,"select A,B,C,D,' ',' ' where D = 'No'",1)` with the ‘ ‘ and ‘ ‘ columns added to be blank. Note the first one has a single space and the second has a double space (they had to be different).

Then we can combine with { } notation, like so:

```={query(\$A\$1:\$F\$5,"select * where D = 'Yes'",1); query(\$A\$2:\$F\$5,"select A,B,C,D,' ',' ' where D = 'No'",0)}```

Finally I wrap this whole thing in another query to resort and remove the redundant header row from the second inner query:

```=query({ query(\$A\$1:\$F\$5,"select * where D = 'Yes'",1); query(\$A\$2:\$F\$5,"select A,B,C,D,' ',' ' where D = 'No'",0) },"select * order by Col1 offset 1",1)```

Hope that helps!

Ben

Hi Ben,

thanks for this. It works for me – including sorting the rows by date in the wrapper query.
Turns out there is still a lot for me to learn about spreadsheets 🙂

Thanks,

46. Cynthia Madu says:

Hi,

I was wondering if there is a way to write the query to disregard capitalization. Essentially, I am doing a count and would like United States to be the same as United states or united states.

1. Ben says:

Hey Cynthia,

Yes, you can do this by wrapping your original data with the LOWER() function to make all your data lowercase. Then you can wrap the output with the PROPER() function to capitalize again. There are two nuances though: 1) you need to refer to your columns by Col1, Col2, … etc. and not A, B, … and 2) you need to make use an ArrayFormula with the LOWER and PROPER.

Your formula should end up looking something like this example (my data was in A1:B10):

`=ArrayFormula(proper(query(ArrayFormula(lower(A1:B10)),"select Col1, count(Col2) group by Col1",1)))`

Ben

1. Cynthia Madu says:

Thanks so much for that, Ben!

Are there more of these videos?

47. Cynthia Madu says:

Hi Ben,
I have another question about the query and array formula. Is there a way to set up the formula so that it does not matter where the location of the data is on that list. Let’s say I want to count Column 1 and group it by column 2 even if they do not match up one-by-one? So Apple is number 6 on column 1 but 22 on column 2.

Cynthia

1. Ben says:

I don’t quite follow your question, but the categories you want to group by must all be in the same column, and the data you want to aggregate (e.g. sum) in those categories must be in another column. Hope that helps!

Ben

1. David H says:

I’m having a little trouble following your question also, but if I am reading it right I think a solution might be to create a “helper” column that goes with the original data. This column could concatenate both columns of information into one. Then your query can do your count based on whether the helper column contains “Apple.” Hope that also helps.

48. Joao Monteiro says:

Hi Ben,
I’ve researched but still have not found a clear answer that would allow me to solve the situation.
I want to use the query function to get an array whose column X is less than the time defined in a cell Y.

=QUERY(A:E,”select A, B, D, E where D < timeofday '"&H1&"' " )

I get the error

Error
Unable to parse query string for Function QUERY parameter 2: Invalid timeofday literal [43099.8496359375]. Timeofday literals should be of form HH:mm:ss[.SSS]

The H1 cell is TIME formatted hh:mm:ss

I really would like you to help me.

1. Ben says:

Hi Joao,

Question: do you have time only in column D (not date times)? Want to make sure you’re comparing a time against a time, not a datetime against a time if that makes sense.

The issue is likely that the time in H1 is not in a string literal format for the QUERY function. If you wrap H1 in a TEXT function, then it’ll be passed into the query as a string rather than a decimal number (how a time is stored). E.g. something like this:

`=query(A:E,"select * where D < timeofday'"&text(H1,"HH:mm:ss")&"'",1)`

Hope that helps!

Cheers,
Ben

49. Philip says:

I am trying to query across sheets and bring results grouped and organized by datetime.
It is financial data with a particular timestamps that not perfectly aligned, so I would like to group the results into 5 min groups or something. Is that possible?

50. Kleber de Oliveira says:

Excellent tutorial! Many thanks.

1. Ben says:

Thanks!

51. Arif Ahmed says:

Hi,

I have built some queries from my table. I want to use this queries as buttons or links, so that client can run them and check accordingly.

OR , is there any way to save those queries and run them one by one?

Thanks, Arif

1. Ben says:

Hi Arif,

Not as such with formulas alone. They are active once you’ve entered them into the cell. If you want to start having triggers then you’ll need to look at Apps Script code.

Thanks,
Ben

52. Sławek says:

Hi Ben,

great greetings from Poland. I like your site very much.

I have a question. Let’s assume we have a matrix with three columns: Task, CategoryOfTask, Time. I would like to build (using Query function)
CategoryOfTask, Sum (Time), Precentage
It is easy to build the first two columns, but I have no idea how to generate third column. Is it possible?

1. Ben says:

Hi Slawek,

Great question! If you create a helper cell with the total sum for the time column, you can then reference that in your query function e.g.

`=query(\$A\$1:\$C\$100,"select A, sum(C), sum(C)/"&E1&" group by A",1)`

where E1 would have a formula like:

`=sum(C1:C100)`

Maybe there’s a fancy array method to do in a single formula, but I haven’t explored it…

Cheers,
Ben

53. Sara says:

Hi Ben,

I have a sheet with column school name. The only thing I need to do is filter out school’s name that starts with “s”, “u”, “v”, and “w”. I found 2 different solutions with LEFT formula and filters, and IF formula and filters. But I really want to know how can I do it with QUERY.

I tried to use something similar you wrote in the article: =QUERY(countries,”SELECT B, C, D WHERE C = ‘Europe’ “,1). But it’s not working.

1. Ben says:

Hi Sara,

You’ll need to use wildcards and regular expression in your QUERY function to achieve this, which are not mentioned above, so it’s a little bit tricky. But here you go:

E.g. to get everything NOT starting with s, u, v or w use:

`=query(\$A\$2:\$B\$8,"select A, B where A matches '^[^s].*' and A matches '^[^u].*' and A matches '^[^v].*' and A matches '^[^w].*'",1)`

E.g. to get everything starting with s, u, v or w, use

`=query(\$A\$2:\$D\$100,"select A, B, C where A matches 's.*' or A matches 'u.*' or A matches 'v.*' or A matches 'w.*'",1)`

Hope that helps!

Ben

54. Lindsey B. says:

Great post, thanks!

I’m attempting to use a Query formula with a “match” condition… I think I have it figured out but then I can’t copy the formula successfully down the column. Do you have any suggestions? This is the formula I am using: =QUERY(‘Cumulative Credits’!A:H, “Select H where A matches ‘^.*(” & A2 & “).*\$'”)

When I try to column down the column in the results sheet I get a lot of reference errors. Is it a matter of using absolute cell references?

1. Ben says:

Hi Lindsey,

The Query function outputs to a range (i.e. a table) not just a single cell, so it needs space around it to expand to the dimensions of the output table. If there’s anything in any cells stopping this you’ll get a #REF! error.

So in your example there may be more that one answer being returned by the Query function. Note, it also includes a header in the output, so it takes up two rows.

Hope that helps!

Ben

55. Ben says:

Hi Ben,

Great post. This has been very helpful.

I am trying to query a column of dates for expired insurances. The formula I’m using is:

`=QUERY(MS, "select B where G < date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",1)`

This works fine but there are some policies that only expire when cancelled, so have 'Until cancelled' in column G. I want to exclude these from the query but am struggling.

Any ideas?

Many thanks,
Ben

1. Ben says:

Hi Ben,

These should already by excluded by the WHERE clause, since the “Until cancelled” ones will fail the logical test to see if the date is less than the threshold. Are you seeing them returned by the query?

Ben

56. Salman Mushir says:

Thanks Ben. This is great resource, I’ve watched your webinar on this as well. Invaluable.

I have a question regarding ’rounding out the values’. I would like to remvoe the decimal values that are resulted through calculations within the query. Ideally something like =ROUND(QUERY(B:C, “select avg(B)”,1)). Can I achieve this? How about if it is for a single value?

1. Ben says:

Hi Salman,

There’s no way to do this inside of the QUERY function – it doesn’t have anything built in.

The easiest thing to do is simply add a ROUND() function helper column next to the QUERY output.

You can do in the same formula as the QUERY but it gets complex (because of non-numeric values, like headers). Here are some example formulas:

Single value example:

e.g. sum the values in column E, round the total. The index function is needed to remove the header added by the query function.

`=round(index(query(A1:E5,"select sum(E)",1),2),0)`

Single column example:

e.g. query returns a column of numbers that we want to round. The iferror function catches the error with the heading value and renames the column to “Result”. The ArrayFormula is necessary to round each row.

`=ArrayFormula(iferror(round(query(A1:E5,"select E",1),0),"Result"))`

Multi-column example:

e.g. the query returns a column of numbers we want to round and a column of text values (names, say). The IF function tests if the query output is a number or not, rounds it if it is, or leaves it alone if it’s not. The ArrayFormula is needed to operate on each value.

```=ArrayFormula(if( isnumber(query(A1:E5,"select A, E",1)), round(query(A1:E5,"select A, E",1),0), query(A1:E5,"select A, E",1) ))```

(Will be a slow formula, so not best practice.)

Hope that helps!

Ben

57. Candace says:

Hi Ben,

I have a listing of cemeteries that I”m trying to make searchable by certain criteria, based on drop-down (and dependent drop-down) lists. I’m having trouble referencing the menu cells in the query formula.

Perhaps I’m missing something?

58. Hi Ben,
What na great resource!

With your help I’ve been able to get this query to work =QUERY(GSC_3_Months,”SELECT A, B, C, D, E WHERE A contains “word” and E < 20",1)

But I'm trying to get it to work when I replace the string "word" with a value in a cell (example G1) so I can just type in a cell to change the results:
=QUERY(GSC_3_Months,"SELECT A, B, C, D, E WHERE A contains G1 and E < 20",1)

What am I doing wrong?

59. Trevor says:

This is amazing! One follow-up: What if we wanted to list, e.g., your final example, but then also include the country in that continent with the highest population, and then also its population? So in your final example, column L would include, say, China, and M would list China’s population. Is there any way to do this dynamically? I’ve tried a bunch of different options but keep drawing blanks.

60. Cara says:

Dear Ben,

Thank you for your very easy to follow guides and posts! I am new to using the query function, here is what I would like to do:

I have an export of a google calendar in Sheet1 which has a column with an event title (Column A) and a column with the date (column B)

I would like to pull the event title in Sheet 1, column A into another sheet (Sheet2) if the date for that event matches a cell with that date in Sheet2. e.g. “meeting bob” on 10/07/2018 would be pulled if the date in Sheet 2, E1 was equal to 10/07/2018.

I am using the following formula, but keep getting a “formula parse error”. What am I doing wrong?

=query(Sheet1!A:D, “select A where B = “Sheet2!E1” “,0)

Any help would be much appreciated!

Thanks

Cara

61. Hi Ben,
What if I wanted to have a chart that would automatically fill the corresponding info in the following rows? Using your chart as an example, lets say I can choose a Rank from a dropdown list. As soon as I choose Rank 3, the following cells autofill with United States, North America, and 320, 050,716.

Is there a way to do that?
Thanks

62. Cos says:

Hi and thanks for the article. I have a question. How can one select only the country with the highest population for each continent?

63. Mickhail says:

Really helpful, you have by far the most concise methods of teaching that I have found on the youtubes.

64. Anne says:

This is amazing. Thanks for explaining how to use it.

Can you say what the 1 is for at the end of the formula string?

Thanks!

1. Ben says:

Hey Anne,

The 1 at the end of the formula is to indicate how many header rows you have (so you’d typically have a 0 or a 1 here, but it could be any value). It’s an optional argument, so you can omit it and Google will figure it out based on your data. I tend to include it though, out of habit and to ensure that it matches my header rows.

Cheers,
Ben

65. shruti gupta says:

Hi,

I am try to use the =QUERY(Query,”SELECT F, COUNT (H) GROUP BY F “) , in my query function. However my column H contains both alphabets and numbers. So while counting it is only counting the cells which have numbers. Is it a limitation of Query Function or do I need to modify the formula?

66. Georg Pawlowski says:

Hi Ben,
Excellent tutorial!
My head is overflown with the many new information from your site and I may have overseen a solution to my (probably simple) problem.
Basically I want to divide values by an aggregate value, as
=(query(All!A2:Z500, “Select W, (W/sum(W)) Where Z Is Null”,
which obviously does not work.
I am a novice and your advice is highly appreciated.
Kind regards, Georg

67. Joslyn says:

Thank you for your tutorials! I have been successful with my query when the cell contains one piece of information. I am stuck, now, when a single cell has more than one. I would like to make it so I can gather data from a row Where B contains at least 1. The cell looks something like 1, 2, 3 since multiple checkboxes were selected from a Form.

My existing query is:

=QUERY(‘Form Responses 1’!A:K,”Select * Where B = 1 “)

What can I change the = to so that A:K with a 1 in column B (even if there is other info) is filtered out?

68. Paul Boder says:

I have never used any spreadsheet other than typing values into the cells and using things like sort from the menu. I have done lots of programming including plenty of sql. I happened to have a spreadsheet where I needed to group by one column and show the max value on another.

So how do I even put any sort of function in a spreadsheet? And how do I group things? With the help of a couple of other sites but especially this page it’s all become clear. Perhaps I’ll delve into your main course.

Thanks a lot.

1. Corina says:

Can there be a sql statement that uses the where clause to display rows for only one filter condition? For example, when a user goes to a google sheet, the only rows displayed from the google sheet would be those that had the same email address as the person accessing the google sheet (i.e. only see rows of my own data)

69. Thanks for sharing this productive SQL queries for google sheets. Now I can make use my offline database to fwtech the data to google sheet.

70. Rose says:

Hello, I am using this query =QUERY(Merchandising!A:AL,”Select * where C = ‘Merchandise Pull-out'”) somehow, it aggregates the contents of the all the other columns that does not contain “Merchandise Pull-out” in one row.

Can you help me with this? I used this formula before and had no issues with it.

Thank you.

71. Mattia says:

Hi Ben, congrats for your courses.
I have a question; in your course (Advanced Formula 30 Day Challenge_Video “Query II”) minute 3:44, how can I do this?

Property type | Sales Price
House | \$ 1,188,565
House | \$ 1,145,078
House | \$ 736,614
Townhouse | \$ 943,637
Townhouse | \$ 778,282
Condo | \$ 682,667
Condo | \$ 486,653
Condo | \$ 391,871

Have a nice day.

72. Prathamesh Padosakar says:

Hi Regarding Query number 12 which is (=QUERY(countries,”SELECT B, C, D WHERE C = ‘Europe’ “,1))

SO which query should use if we want to sum of the output of population

73. Margot says:

I have a sheet with multiple values in Column F and multiple values in Column I. I want to see a count() of each value in Column I by each value in Column F. ex:
Column I | Column F | co. of I&F
Bear | Tree | 2
Bear | Den | 4
Rabbit | Warren | 8
Badger | Warren | 1

Is there a way to do this in a single query?

74. Hello!

Thank you for useful content!

Need some help on my issue.

I did a list of content I want to output by checking it like in “to do list” =QUERY(B131:C142;”SELECT B WHERE C=TRUE”;-1)

So I’ve been trying to write two query formulas (like this =QUERY(B131:C142;”SELECT B WHERE C=TRUE”;-1);QUERY(B144:H150;”SELECT *”;-1) to output second query result right below, so that it would be as a summary of previously selected content. (Note: there are manual formulas in range (J131:P136), not query). But the issue is – this summary has more columns that the first query data and the formula doesn’t work. But I really need this summary to be followed by outputed data before. No gap between allowed.
See a screenshot using the link below.

Can someone help me?

75. Christie says:

I have a column G that has both dates and text. For column GF, the only thing I need is for it to be displayed, but the query brings over only the dates. Cells where the text should be displayed are blank. I am not sure how to either adjust the function to bring over both text and date or how to format the column to be all text without converting the dates to their datevalue.

=QUERY(Mar19Data,”SELECT F, I, G, E, K WHERE A = ‘Digital’ and E = ‘KO'”,1)

76. Sebastine says:

If a table is populated in Column ‘BY’ , Query function does not respond to instruction to ‘ORDER BY’ as there is multiple ‘BY’ in the Query. So, how to order a table which has a Column ‘BY’ using ‘ORDER BY’?

77. Heidi says:

Hello!
This is fantastic! Thank you very much for posting. I hope you can help me with a question here.
I am using a QUERY in Google Sheets that is counting the number of responses to survey questions. Because it is a branched survey there are blank cells between the responses. Is there a way to query count and exclude any blank cells?
Here is my formula:
=QUERY(RESPONSES!A1:E111, “Select E, count (A) group by E”,1)

78. Benjamin Phillips says:

Hey Ben (from one to another!),

I have what I think is a simple application, but one I’m getting stuck on.

I have a set of clothing brands. They each belong to a specific target market, of which there are 3. I would like to show the top 5 performing brands by segment, meaning there should be 15 rows total (5*3, assuming there are at least 5 stores per segment). However, I can’t seem to do that with the limit function, as that limits the total rows in the output.

Here’s the code I was trying, in case it helps to clarify:
=query(input!A1:H,”select B,count(A),D group by D[5],B[5] order by B,count(A)”,0)

Thank you!
Ben