Cara menggunakan google sheets criterion

It allows you to use data commands to manipulate your data in Google Sheets, and it’s incredibly versatile and powerful.

This single function does the job of many other functions and can replicate most of the functionality of pivot tables.

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

Google Sheets QUERY Function Syntax

=QUERY(data, query, [headers])

It takes 3 arguments:

1. the range of data you want to analyze
2. the query you want to run, enclosed in quotations
3. an optional number to say how many header rows there are in your data

Here’s an example QUERY function:

=QUERY(A1:D234,"SELECT B, D",1)

The data range in this example is A1:D234

The query statement is the string inside the quotes, in green. In this case, it tells the function to select columns B and D from the data.

The third argument is the number 1, which tells the function that the original data had a single header row. This argument is optional and, if omitted, will be determined automatically by Sheets.

It’s one of the Google-only functions that are not available in other spreadsheet tools.

QUERY Function Notes

The keywords are not case sensitive, so you can write “SELECT” or “select” and both work.

However, the column letters must be uppercase: A, B, C, etc. otherwise you’ll get an error.

The keywords must appear in this order (of course, you don’t have to use them all):

• select
• where
• group by
• order by
• limit
• label

You’ll see examples of all of these keywords below.

There are a few other keywords but they are much less common. See the .

Google Sheets QUERY Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

Google Sheets QUERY Function Examples

If you want to follow along with the solutions, please make a copy of the Google Sheet template above.

This is what our starting data looks like:

In this tutorial, I have used a named range to identify the data, which makes it much easier and cleaner to use in the QUERY function. Feel free to use the named range “countries” too, which already exists in the template.

If you’re new to named ranges, here’s how you create them:

Select your data range and go to the menu:

Data > Named ranges…

A new pane will show on the right side of your spreadsheet. In the first input box, enter a name for your table of data so you can refer to it easily.

SELECT All

The statement SELECT * retrieves all of the columns from our data table.

To the right side of the table (I’ve used cell G1) type the following Google Sheets QUERY function using the named range notation:

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

Notes: if you don’t want to use named ranges then that’s no problem. Your QUERY formula will look like this:

=QUERY(A1:D234,"SELECT *",1)

For the remainder of this article, I’ve used the named range “countries” but feel free to continue using the regular range reference A1:D234 in its place.

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 QUERY! Pat yourself on the back.

SELECT Specific Columns

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)

This time we’ve selected only columns B and D from the original dataset, so our output will look like this:

Important Note

Remember, our QUERY function is in cell G1, so the output will be in columns G, H, I, etc.

The B and D inside the QUERY select statement refer to the column references back in the original data.

WHERE Keyword

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

Modify your Google Sheets QUERY function to select only countries that have a population greater than 100 million:

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

Our output table is:

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

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

Notice how there are single quotes around the word ‘Europe’. Contrast this to the numeric example before which did not require single quotes around the number.

Now the output table is:

ORDER BY Keyword

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

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

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

The output table:

Modify your QUERY formula to sort the data by country in descending order, Z – A:

=QUERY(A1:D234,"SELECT B, D",1)0

Output table:

LIMIT Keyword

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

Let’s add a LIMIT keyword to our formula and return only 10 results:

=QUERY(A1:D234,"SELECT B, D",1)1

This now returns only 10 results from our data:

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.

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(A1:D234,"SELECT B, D",1)2

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

The output table this time is:

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

LABEL Keyword

That heading for the arithmetic column is pretty ugly right? Well, we can rename it using the LABEL keyword, which comes at the end of the QUERY statement. Try this out:

=QUERY(A1:D234,"SELECT B, D",1)3

=QUERY(A1:D234,"SELECT B, D",1)4

Aggregation Functions

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

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

=QUERY(A1:D234,"SELECT B, D",1)5

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

GROUP BY Keyword

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 keyword is used with aggregate functions to summarize data into groups as a pivot table does.

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

=QUERY(A1:D234,"SELECT B, D",1)6

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

The output for this query is:

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

=QUERY(A1:D234,"SELECT B, D",1)7

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

=QUERY(A1:D234,"SELECT B, D",1)8

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

The output of this query is:

Advanced Google Sheets QUERY Function Techniques

How to add a total row to your Query formulas

How to use dates as filters in your Query formulas

There are 4 more keywords that haven’t been covered in this article: , , and .

In addition, there are more than we’ve discussed above. For example, there are a range of scalar functions for working with dates.

Suppose you have a column of dates in column A of your dataset, and you want to summarize your data by year. You can roll it up by using the YEAR scalar function:

=QUERY(A1:D234,"SELECT B, D",1)9

For more advanced techniques with the QUERY function, have a watch of this lesson from the Advanced 30 course:

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

Other Resources

The QUERY Function is covered in days 14 and 15 of my free Advanced Formulas course for Google Sheets: Learn 30 Advanced Formulas in 30 Days

Bagaimana cara menggunakan Google sheet?

Bagaimana cara menghitung rata

Apa saja yang bisa dilakukan dengan Google sheet?

Apa saja rumus spreadsheet?