⚡ The 30-Second Quick Answer
If you need to cross-aggregate raw CRM data across multiple dynamic conditions (such as specific regions, minimum deal values, and date ranges selected via dropdown cells), here is the production-ready formula:
When raw webhook exports from CRM systems land in Google Sheets, team leads often rush to create dozens of helper columns and rigid SUMIFS matrices. That approach breaks the moment you add a new team member or want to group data across both dimensions and time frames simultaneously.
The Google Sheets QUERY function acts like an in-sheet SQL engine. By combining data grouping, numerical aggregations, dynamic cell references, and strict date parsing into a single dynamic formula, you can build self-updating cross-tab reports that recalculate cleanly.
1. Anatomy & Syntax: Combining Text, Numbers, and Dates
The hardest part of mastering QUERY is escaping strings properly when referencing other cells. Google Sheets requires different wrapping conventions depending on whether your parameter is text, a number, or a date:
| Data Type | Syntax Rule | Formula Pattern |
|---|---|---|
| Static Text | Wrap in single quotes | WHERE D = 'Closed Won' |
| Dynamic Text Cell | Wrap in single and double quotes | WHERE C = '"&I1&"' |
| Dynamic Number Cell | Wrap in double quotes only | WHERE E >= "&I3&" |
| Dynamic Date Cell | Prefix with date keyword and format as yyyy-mm-dd |
WHERE G >= date '"&TEXT(I2,"yyyy-mm-dd")&"' |
2. Step-by-Step Practical Walkthrough
Let's walk through a realistic raw pipeline export containing SaaS account data across columns A through G.
The Source Data (Range A1:G9)
| Opp ID (A) | Sales Rep (B) | Region (C) | Deal Stage (D) | Contract Value (E) | Cycle Days (F) | Close Date (G) |
|---|---|---|---|---|---|---|
| OPP-101 | abc | North America | Closed Won | $4,500 | 24 | 2026-01-15 |
| OPP-102 | xyz | EMEA | Closed Won | $8,200 | 41 | 2026-01-18 |
| OPP-103 | abc | North America | Qualified | $3,200 | 15 | 2026-02-01 |
| OPP-104 | def | North America | Closed Won | $12,000 | 55 | 2026-02-10 |
| OPP-105 | abc | North America | Closed Won | $6,100 | 30 | 2026-03-05 |
| OPP-106 | xyz | EMEA | Closed Lost | $5,000 | 60 | 2026-03-12 |
Configuring the Dynamic Filters
Set up two user-controlled filter cells in your sheet to drive the query dynamically:
- Cell I1: Region dropdown (e.g.,
North America) via Data > Data validation > Dropdown. - Cell I2: Start Date input (e.g.,
2026-01-01).
Building the Complete Aggregation Query
We want to aggregate Total Closed-Won Contract Value (MRR) and Average Sales Cycle Length grouped by Sales Rep and Region, ordered from highest revenue to lowest. Enter this formula in cell K1:
A2:G,
"SELECT B, C, SUM(E), AVG(F) "
& "WHERE lower(D) = 'closed won' "
& "AND lower(C) = '" & LOWER(I1) & "' "
& "AND G >= date '" & TEXT(I2, "yyyy-mm-dd") & "' "
& "GROUP BY B, C "
& "ORDER BY SUM(E) DESC "
& "LABEL SUM(E) 'Total Closed MRR', AVG(F) 'Avg Sales Cycle (Days)'",
0
)
QUERY strings are strictly case-sensitive. If an entry is typed as Closed won or closed WON, standard equality checks will skip those records. Wrapping both the column identifier and the criteria in lower() (e.g., lower(D) = 'closed won') ensures bulletproof matches regardless of manual input inconsistencies.
3. Excel vs. Google Sheets: Key Differences
If you are migrating between Microsoft Excel and Google Sheets, keep these structural mechanics in mind:
| Feature | Google Sheets | Microsoft Excel |
|---|---|---|
| SQL-Style Querying | Native QUERY() function available in any standard formula bar. |
Requires Power Query (M Code) or combinations of GROUPBY() / PIVOTBY() in Microsoft 365. |
| Dynamic Spilling | Automatic array output with custom headers via LABEL. |
Spills dynamically only when using modern dynamic array formulas. |
| Mixed Data Types | Columns with mixed text and numeric data default to majority type; minority entries turn null. | Preserves mixed cell types natively across recalculations. |
4. Three Common Pitfalls & How to Fix Them
1. The #VALUE! Date Parsing Error
Why it happens: Passing a standard date cell directly (e.g., "WHERE G >= '"&I2&"'") injects localized text like 01/01/2026, which the Query engine fails to interpret.
The Fix: Always cast the cell explicitly with date '"&TEXT(I2, "yyyy-mm-dd")&"'.
2. The CANNOT_GROUP_WITHOUT_AGG Error
Why it happens: Selecting unaggregated non-numeric columns alongside aggregation functions without including them in the GROUP BY clause.
The Fix: Every column in your SELECT statement that does not have an aggregation function applied (like SUM, AVG, or COUNT) must be explicitly listed in GROUP BY.
3. Silent Missing Rows from Mixed Data Formats
Why it happens: If your numeric column contains manual annotations (e.g., $4,500 (test)), QUERY assigns that column a pure numeric type and silently ignores text values entirely.
The Fix: Keep raw data columns clean and strictly typed, or use array formatting like QUERY(INDEX(TO_TEXT(A2:G)), ...) when parsing mixed datasets.
5. Actionable Takeaways
- Construct SQL strings modularly using
&concatenations to make updates readable. - Normalize user input and source columns with
lower()to prevent casing mismatches. - Use
LABELclauses at the end of the query string to define clean column headers directly in the formula output.
Frequently Asked Questions
Can I reference a range from another sheet tab inside QUERY?
Yes. Reference the source tab in the first argument (e.g., QUERY('Raw Data'!A2:G, "SELECT ...")). The column letters remain identical to the source range.
How do I leave a dropdown filter blank to show all records?
Wrap the condition in an IF check: "WHERE 1=1 " & IF(ISBLANK(I1), "", "AND lower(C) = '"&LOWER(I1)&"'"). This leaves the filter open when no dropdown value is selected.
Why did my aggregated column headers default to uppercase labels like "avg Sales Cycle"?
QUERY automatically generates default technical labels for aggregated columns unless you explicitly override them using the LABEL clause at the end of the query.
Comments