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 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
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.