WHERE clause for filtering in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Filtering rows with WHERE clause in Google Sheets QUERY

Given a sheet named 'Sales' with columns Date, Product, and Amount, which QUERY formula filters rows where Amount is greater than 100?

Google Sheets

QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 100", 1)

A QUERY(Sales!A:C, "SELECT A, B, C WHERE Amount > 100", 1)

B QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 100", 1)

C QUERY(Sales!A:C, "SELECT A, B, C WHERE C >= 100", 1)

D QUERY(Sales!A:C, "SELECT A, B, C WHERE B > 100", 1)

💡 Hint

Remember that column letters in QUERY use letters like A, B, C, not column names.

In Google Sheets QUERY, you filter by column letters, not column headers. So 'WHERE C > 100' filters rows where the third column (Amount) is greater than 100.

🔧 Formula Fix

intermediate

Identify the syntax error in this QUERY with WHERE clause

Which QUERY formula has a syntax error when filtering rows where Product equals 'Apple'?

A QUERY(Sales!A:C, "SELECT A, B, C WHERE B == 'Apple'", 1)

B )1 ,"'elppA' = B EREHW C ,B ,A TCELES" ,C:A!selaS(YREUQ

C QUERY(Sales!A:C, "SELECT A, B, C WHERE B = 'Apple'", 1)

D QUERY(Sales!A:C, "SELECT A, B, C WHERE B = \"Apple\"", 1)

💡 Hint

Check the operator used for equality in QUERY language.

In QUERY language, the equality operator is a single '=' sign. Using '==' causes a syntax error.

❓ optimization

advanced

Optimizing QUERY with multiple WHERE conditions

Which QUERY formula efficiently filters rows where Amount is greater than 50 and Product is 'Banana'?

A QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 OR B = 'Banana'", 1)

B QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 AND B = Banana", 1)

C QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 AND B = 'Banana'", 1)

D QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 50 AND B == 'Banana'", 1)

💡 Hint

Use AND to combine conditions and single '=' for equality.

Option C correctly uses AND to combine conditions and uses single '=' with quotes around 'Banana'.

🔧 Formula Fix

advanced

Why does this QUERY return no rows?

Given the formula QUERY(Sales!A:C, "SELECT A, B, C WHERE C > 100 AND B = 'Orange'", 1), why might it return no rows even if data exists?

A Because the QUERY function requires numeric columns to be referenced by name, not letter.

B Because the WHERE clause uses AND instead of OR, which is invalid syntax.

C Because column C is text, so '> 100' causes an error and returns no rows.

D Because the data has no rows where Amount is greater than 100 and Product is exactly 'Orange'.

💡 Hint

Check if the data actually matches both conditions at the same time.

If no rows satisfy both conditions simultaneously, QUERY returns no rows. The syntax and column references are correct.

🧠 Conceptual

expert

Understanding WHERE clause behavior with NULL or empty cells

In Google Sheets QUERY, what happens when you filter with WHERE C > 0 but some rows in column C are empty or contain text?

A Rows with empty or text values in column C are excluded because they do not satisfy the numeric condition.

B QUERY throws an error because of mixed data types in column C.

C Rows with empty cells are included, but rows with text are excluded.

D Rows with empty or text values are treated as zero and included if zero > 0 is true.

💡 Hint

Think about how QUERY treats non-numeric values in numeric comparisons.

QUERY excludes rows where the condition cannot be evaluated as true, so empty or text cells in numeric comparisons are excluded.