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 for conditional aggregation in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=SUMIFS(C2:C10,B2:B10,"Paid",D2:D10,E2)Google Sheets
=QUERY(A1:D10,"SELECT D, SUM(C) WHERE B = 'Paid' GROUP BY D LABEL SUM(C) 'Paid Revenue'",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
- 1Set up your data with columns: Order ID (A), Status (B), Amount (C), Region (D) with headers in row 1.
- 2To get total paid revenue grouped by region: =QUERY(A1:D10,"SELECT D, SUM(C) WHERE B = 'Paid' GROUP BY D LABEL SUM(C) 'Paid Revenue'",1).
- 3The WHERE clause applies the condition (Status = 'Paid') before aggregation — so only qualifying rows contribute to SUM(C).
- 4Add multiple aggregations in one formula: "SELECT D, SUM(C), COUNT(A), AVG(C) WHERE B = 'Paid' GROUP BY D LABEL SUM(C) 'Total', COUNT(A) 'Orders', AVG(C) 'Avg Order'".
- 5To filter by multiple conditions, combine with AND: WHERE B = 'Paid' AND C > 500 excludes small orders before summing.
Tips
- ORDER BY SUM(C) DESC at the end of the query string ranks regions by revenue — useful for dashboards: "SELECT D, SUM(C) WHERE B = 'Paid' GROUP BY D ORDER BY SUM(C) DESC LABEL SUM(C) 'Paid Revenue'".
- QUERY always applies WHERE before GROUP BY — you cannot filter on an aggregated value like SUM(C) > 1000 in the WHERE clause. If you need post-aggregation filtering, nest the QUERY or use a helper range.
- In Excel, the SUMIFS equivalent is =SUMIFS(C:C, B:B, "Paid", D:D, region_cell). For multiple regions at once, use SUMPRODUCT or add a helper column and use a pivot table.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can I filter on the aggregated result (equivalent to SQL HAVING)?
Not directly — QUERY does not support a HAVING clause. The workaround is to wrap the QUERY in another QUERY: =QUERY(QUERY(A1:D10,"SELECT D, SUM(C) WHERE B = 'Paid' GROUP BY D",1),"SELECT * WHERE Col2 > 1000",1) where the outer QUERY filters on the aggregated column.
How do I count distinct values inside a group?
Use COUNT on a column you know has unique values per row (like an order ID column A): SELECT D, COUNT(A) WHERE B = 'Paid' GROUP BY D. This gives the number of paid orders per region.
Is conditional aggregation faster in QUERY or SUMIFS?
For a single criterion SUMIFS is faster to calculate. QUERY with GROUP BY becomes more practical when you need multiple aggregations or dynamic output — it spills a full summary table instead of requiring one SUMIFS formula per cell.
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 use QUERY with WHERE, AND, OR
Filter QUERY results in Google Sheets using WHERE with AND and OR conditions. Excel users can replicate this with FILTER using * for AND and + for OR.
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.