Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use QUERY with WHERE, AND, OR in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=FILTER(A2:C10,(B2:B10="Electronics")+(B2:B10="Clothing"))Google Sheets
=QUERY(A1:C10,"SELECT A, B, C WHERE (B = 'Electronics' OR B = 'Clothing') AND C > 500",1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Confirm your data layout: Order ID (A), Category (B), Amount (C) with headers in row 1.
- 2Click a destination cell — such as E1 — and enter: =QUERY(A1:C10,"SELECT A, B, C WHERE (B = 'Electronics' OR B = 'Clothing') AND C > 500",1).
- 3The WHERE clause filters to rows where Category is Electronics OR Clothing, AND the Amount exceeds 500.
- 4Parentheses control precedence just as in SQL — wrap OR conditions in parentheses when combining with AND to avoid ambiguity.
- 5To negate a condition use NOT: WHERE NOT (B = 'Electronics') excludes that category entirely.
Tips
- String literals inside the QUERY string must use single quotes: WHERE B = 'North', not double quotes, because the outer formula string already uses double quotes.
- Numeric conditions do not use quotes: WHERE C > 500 is correct; WHERE C > '500' will cause a type error.
- If you need more than two OR branches, QUERY handles them cleanly: WHERE B = 'A' OR B = 'B' OR B = 'C'. In FILTER you'd write (B='A')+(B='B')+(B='C') and test >0.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does QUERY support NOT LIKE or NOT IN?
QUERY supports NOT with conditions: WHERE NOT B = 'Electronics'. For a list exclusion, chain them: WHERE B != 'Electronics' AND B != 'Clothing'. There is no native IN(...) operator in the Google Visualization API query language.
Why does my WHERE clause return no results even though the data matches?
Case sensitivity is the most common cause. QUERY string matching is case-sensitive by default. Ensure 'Electronics' matches the exact capitalisation in your data, or use LOWER() in the formula and LOWER(B) = 'electronics' in the query string.
Can I combine WHERE with GROUP BY?
Yes. WHERE always comes before GROUP BY in the query string: SELECT B, SUM(C) WHERE C > 500 GROUP BY B. QUERY respects the same clause order as SQL.
More on QUERY (Google Sheets SQL)
See all →How-to
How to use QUERY to filter data
Use Google Sheets QUERY with a WHERE clause to filter rows dynamically — Excel users can use FILTER as the equivalent.
Error fixFix QUERY error
QUERY parse error in Google Sheets means the SQL-like query string has a syntax mistake. Find the exact error and fix it here.
How-toHow to use QUERY to sum by group
Use Google Sheets QUERY with GROUP BY and SUM to aggregate data by category — use SUMIF for the Excel equivalent.
How-toHow to use QUERY to group by multiple columns
Use QUERY GROUP BY with multiple columns in Google Sheets to create multi-level summaries. Excel alternative uses SUMIFS with multiple criteria ranges.
How-toHow to create a running total with QUERY
Build a running total in Google Sheets by combining QUERY for ordering with ARRAYFORMULA and SUMIF. Excel alternative uses a simple SUMIF with a locked anchor row.
How-toHow to combine QUERY with IMPORTRANGE
Combine QUERY and IMPORTRANGE in Google Sheets to filter and aggregate data from another spreadsheet file. There is no Excel equivalent — use Power Query to connect workbooks instead.