What you will build

This lesson reproduces a QUERY column-name error when the data argument is an array expression. In the verified workbook, {A1:B3} with A/B returns #N/A; changing the query string to Col1/Col2 works.

Learning objectives

Open the practice workbook

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.

Clean _result workbook before entering the formula.

Hands-on practice

Step 1: Confirm Example!A1:B3 contains Region/Amount, North/120, and South/240.

Step 2: In D2, enter the broken formula =QUERY({A1:B3},"select A, sum(B) group by A",1). With {A1:B3}, the verified workbook returns #N/A.

D2 shows #N/A for the array formula using A/B; the formula bar shows the broken formula.

Step 3: Keep the same data but replace A/B with Col1/Col2: =QUERY({A1:B3},"select Col1, sum(Col2) group by Col1",1). The result must spill across D2:E4 with Region, sum Amount, North 120, and South 240.

=QUERY({A1:B3},"select Col1, sum(Col2) group by Col1",1)
D2 selected with the Col1/Col2 formula and the correct spill result across D:E.

Enter the formula in D2.

Checkpoint

RegionSum Amount
North120
South240

The result must be generated by the formula in D2 and spill across D:E; do not type 120 or 240 manually.

Common mistakes and fixes

Correction: return to the exact range and command named in the steps, then compare the live sheet with the Region, Amount, , Query source fields.

Independent practice

Temporarily change B2 from 120 to 150 and confirm North recalculates to 150; then restore B2 to 120. Finally add East/80 in row 4, expand the array to {A1:B4}, then remove the test row.

Key takeaways

  • ✓Identify the data shape before choosing QUERY column identifiers.
  • ✓In the {A1:B3} array example, A/B returns #N/A while Col1/Col2 works.
  • ✓QUERY returns two columns: Region and summed Amount.
  • ✓Change one source value to test recalculation, then restore the sample.
  • ✓Do not type expected answers over a formula error.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. What is QUERY's data argument in this lesson?
2. Which identifiers work in the verified array example?
3. How many columns does the correct result have?
4. What is the stronger formula check?

Next lesson

Continue in the learning path with “Fix QUERY date syntax”.