This one is going to be all about the QUERY function. I love using this one. You can do a lot of exciting things with it. Make sure to also read Part 1 and Part 2 to get to know the setup as well as some other functions and neat examples.

QUERY is an awesome function because you can basically use your spreadsheet as a database and query it which means you have a lot of possibilities. In some cases it is also a lot easier to use than doing the same thing by using other functions.

QUERY(data, query, [headers])

- data: this is the range you will query on
- query: the actual query string. I’m going to show you different examples for this one but in case you’re interested in more, you can read about the query language here.
- header: this is optional. It states how many header rows are part of the range (which you can omit by making the range smaller/starting at the next row)

- Use
**select**to choose which columns you want to get

Get the complete column of titles (B):

=QUERY(GR!A2:Z,”select B”) - Use
**where**to choose which rows to get

Get the complete rows of books with less than 500 pages (L):

=QUERY(GR!A:Z,”where L < 500″) (this is equal to “select * where L <500” which means select all columns)

- Get the title (B) and author (C) of books with less than 500 pages (L):

=QUERY(GR!A2:Z,”select B,C where L < 500″)

- Sum up all values in a column

Sum up the pages of all books:

=QUERY(GR!A:Z,”select sum(L)”) - functions: sum, avg, count, max, min

- Get the year of all dates (O):

=QUERY(GR!A2:Z,”select year(O)” - You can also use them in the where clause

Get books read this year [use now() to get the current one]:

=QUERY(GR!A2:Z, “select B,C where year(O) = year(now())”) - functions: year, month, day,…

- Combine multiple conditions with AND or OR

Get books with less than 500 pages or more than 1000 pages:

=QUERY(GR!A2:Z,”select B,C where L < 500 or L>1000″)

Get books with more than 1000 pages (L) and a rating (H) of 5:

=QUERY(GR!A2:Z,”select B,C where L > 1000 and H=5″) - Check of a cell contains a certain value

Get the books from a shelf called “favorites” (Q contains “favorite”):

=QUERY(GR!A2:Z,”select B,C where Q contains ‘favorite’ “) - Check if an entry starts with a certain sub string

Get all authors whose name starts with A:

=QUERY(GR!A2:Z,”select C where C starts with ‘A’ “)

- Order by asc or desc

Get all titles and authors of books, sorted by number of pages

=QUERY(GR!A2:Z,”select B,C order by L”) (asc is automatically used)

Sort them with highest number first:

=QUERY(GR!A2:Z,”select B,C order by L desc”)

- Only return a certain number of rows

Get 5 books with more than 1000 pages:

=QUERY(GR!A2:Z,”select B,C where L>1000 limit 5″)

There are different situations where the function might return an error, which never looks pretty in a spreadsheet but can easily be prevented. Possible errors that might arise even though the function is correct:

- N/A: the query couldn’t find any entries that fit the query
- DIV/0: This can for example happen when you try to calculate the average but there aren’t any values

In all cases you can make it look better by using the IFERROR function.

- If there is an error, return “No books founds”:

=IFERROR(QUERY(GR!A2:Z,”select B,C where L>1000 limit 5″),”No books found”)

Feel free to ask questions in the comments.

For more Code & Design Tutorials see this page.