QUERY function

What you will build

Build a small query summary of paid revenue by region. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The _result workbook shows the clean Date, Region, Agent, Units, Amount, and Status source table before entering QUERY.

Open the source practice workbook in Google Sheets ↗

Work through the example

Step 1: Confirm row 1 contains field headings.

Step 2: In a blank area H1, query B1:F6 selecting Region (Col1) and Amount (Col4), filtered by Status (Col5).

Step 3: Group by Region and label the sum column so a reader understands the measure.

Formula to try

In the verified practice workbook, use H2 in the Formula result area for the formula below. Keep the source table unchanged and compare the displayed result with the checkpoint.

=QUERY(B1:F6,"select Col1, sum(Col4) where Col5 = 'Paid' group by Col1 label sum(Col4) 'Paid amount'",1)

Cell H2 is selected, the formula bar shows QUERY, and the spilled result shows North 200 and South 320.

How this formula works=QUERY(B1:F6,"select Col1, sum(Col4) where Col5 = 'Paid' group by Col1 label sum(Col4) 'Paid amount'",1)

Overall meaning of the whole formula
- Filter rows with Paid status, group them by the first queried column, sum the Amount column for each group, and return the summary table.
QUERY(data, query, [headers])
- `data` is required; it accepts the input range queried as a table. In this lesson the argument passed is `B1:F6`.
- `query` is required; it accepts a Google Visualization API Query Language string. In this lesson the argument passed is `"select Col1, sum(Col4) where Col5 = 'Paid' group by Col1 label sum(Col4) 'Paid amount'"`.
- `headers` is optional; it accepts the number of header rows in data. In this lesson the argument passed is `1`.
Whole-expression evaluation
- QUERY(data, query, headers) runs a Google Visualization query against the supplied range.
- B1:F6 is the input table; inside the query it is addressed as Col1 through Col5.
- select Col1, sum(Col4) returns the first column together with the total of the fourth query column.
- where Col5 = 'Paid' keeps only rows whose fifth query column is Paid.
- group by Col1 aggregates the paid amount by the first query column, while label renames the summed output column.
- The final 1 tells QUERY that the input range contains one header row.
- The result can occupy multiple cells; the spill area must be clear, and any companion condition ranges must have dimensions compatible with the data being processed.
Why the displayed final result appears
- The final 1 tells QUERY that the input range contains one header row.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

After changing E2 for the recalculation test and restoring it, H2 is selected and QUERY returns to North 200 and South 320.

Recalculation check: after the formula returns North = 200 and South = 320, temporarily change the Amount in E2 from 120 to 220. North should recalculate to 300. Restore E2 to 120 and confirm North returns to 200.

Checkpoint

Region	Paid amount
North	200
South	320

The QUERY result spills from H2:I4. In the recalculation test, changing E2 from 120 to 220 raises North from 200 to 300; restoring E2 to 120 returns North to 200 while South remains 320.

Common mistake and correction

If the result is unexpected, inspect the source cells, destination and referenced range in the formula. QUERY uses its own query-language quoting inside the formula string. Col numbers refer to B:F, not worksheet-wide positions.

Independent practice

After following the example, complete this variation without editing the source data: Query all statuses without the where clause and compare the grouped revenue.

Key takeaways

✓Build a small query summary of paid revenue by region.
✓Confirm row 1 contains field headings.
✓QUERY uses its own query-language quoting inside the formula string. Col numbers refer to B:F, not worksheet-wide positions.
✓Query all statuses without the where clause and compare the grouped revenue.

Continue learning

Previous lesson: UNIQUE function; Next lesson: —.

What you will build

Build a small query summary of paid revenue by region. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the source practice workbook in Google Sheets ↗

Work through the example

Step 1: Confirm row 1 contains field headings.

Step 2: In a blank area H1, query B1:F6 selecting Region (Col1) and Amount (Col4), filtered by Status (Col5).

Step 3: Group by Region and label the sum column so a reader understands the measure.

Formula to try

In the verified practice workbook, use H2 in the Formula result area for the formula below. Keep the source table unchanged and compare the displayed result with the checkpoint.

=QUERY(B1:F6,"select Col1, sum(Col4) where Col5 = 'Paid' group by Col1 label sum(Col4) 'Paid amount'",1)

How this formula works=QUERY(B1:F6,"select Col1, sum(Col4) where Col5 = 'Paid' group by Col1 label sum(Col4) 'Paid amount'",1)

Overall meaning of the whole formula
- Filter rows with Paid status, group them by the first queried column, sum the Amount column for each group, and return the summary table.
QUERY(data, query, [headers])
- `data` is required; it accepts the input range queried as a table. In this lesson the argument passed is `B1:F6`.
- `query` is required; it accepts a Google Visualization API Query Language string. In this lesson the argument passed is `"select Col1, sum(Col4) where Col5 = 'Paid' group by Col1 label sum(Col4) 'Paid amount'"`.
- `headers` is optional; it accepts the number of header rows in data. In this lesson the argument passed is `1`.
Whole-expression evaluation
- QUERY(data, query, headers) runs a Google Visualization query against the supplied range.
- B1:F6 is the input table; inside the query it is addressed as Col1 through Col5.
- select Col1, sum(Col4) returns the first column together with the total of the fourth query column.
- where Col5 = 'Paid' keeps only rows whose fifth query column is Paid.
- group by Col1 aggregates the paid amount by the first query column, while label renames the summed output column.
- The final 1 tells QUERY that the input range contains one header row.
- The result can occupy multiple cells; the spill area must be clear, and any companion condition ranges must have dimensions compatible with the data being processed.
Why the displayed final result appears
- The final 1 tells QUERY that the input range contains one header row.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Region	Paid amount
North	200
South	320

The QUERY result spills from H2:I4. In the recalculation test, changing E2 from 120 to 220 raises North from 200 to 300; restoring E2 to 120 returns North to 200 while South remains 320.

Common mistake and correction

Independent practice

After following the example, complete this variation without editing the source data: Query all statuses without the where clause and compare the grouped revenue.

Key takeaways

✓Build a small query summary of paid revenue by region.
✓Confirm row 1 contains field headings.
✓QUERY uses its own query-language quoting inside the formula string. Col numbers refer to B:F, not worksheet-wide positions.
✓Query all statuses without the where clause and compare the grouped revenue.

Continue learning

Previous lesson: UNIQUE function; Next lesson: —.

QUERY function

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

QUERY function

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments