What are all the aggregate functions available in Google Sheets QUERY?

Google Sheets QUERY supports exactly 5 aggregate functions: `sum()`, `avg()`, `count()`, `max()`, and `min()`. These follow the Google Visualization API Query Language spec, which has not added new aggregate functions since launch. There is no `countd()`, `median()`, `stdev()`, or conditional aggregation — for those, you need SUMPRODUCT, MEDIAN(IF(...)) as array formulas, or a tool like DuckDB queried from Sheets.

Do I need GROUP BY when using QUERY aggregate functions?

Yes. If any column in your SELECT is aggregated, every non-aggregated column in the SELECT must appear in a `GROUP BY` clause. Omitting GROUP BY when you have a mix of aggregated and plain columns throws a "Column [x] cannot be used as both an aggregate and non-aggregate column" error. The one exception: if every column in your SELECT is aggregated, GROUP BY is optional and QUERY returns a single summary row.

Why does my QUERY aggregate return the wrong column header?

By default, QUERY names aggregated columns like `sum D` or `count A` — the function name followed by the column letter. If a downstream formula uses that header in a MATCH or VLOOKUP, it'll break when the query structure changes. Use the `LABEL` clause to set explicit headers: `LABEL sum(D) 'Revenue', count(A) 'Deals'`. Place LABEL after GROUP BY and ORDER BY in the query string or QUERY returns a parse error.

Can QUERY calculate a weighted average?

No. QUERY's `avg()` is a simple arithmetic mean — it can't weight values by another column. For a revenue-weighted blended margin or a deal-weighted average rate, use `=SUMPRODUCT('Pipeline'!D2:D500,'Pipeline'!E2:E500)/SUM('Pipeline'!E2:E500)` with SUMIFS filters for the segment you need. The SUMPRODUCT approach also handles multi-condition filtering that QUERY can express, so it's the standard pattern for weighted aggregates in financial models.

How do I count distinct values with QUERY instead of total count?

QUERY's `count()` counts all non-null values, including duplicates. For distinct counts — unique customer IDs, unique invoice numbers — QUERY has no native solution. The standard approach is `=SUMPRODUCT(1/COUNTIF(range,range))` for a simple range, or for filtered distinct counts: `=SUMPRODUCT((filter_condition)/COUNTIFS(id_range,id_range,filter_range,filter_value))`. For a model tracking 1,200 enterprise accounts with multiple transactions each, this runs in under a second on a modern sheet.

Google Sheets QUERY Aggregate Functions: Full List

The function follows the Google Visualization API Query Language spec, which Google's own documentation describes as "a restricted SQL." As of April 2026, the available aggregate functions haven't expanded since the initial release — the list is stable and short.

The Five Aggregate Functions

Function	Syntax	What It Does	Finance Use Case
`sum()`	`sum(Col)`	Total of numeric column	Revenue by region, headcount cost
`avg()`	`avg(Col)`	Mean of numeric column	Average deal size, blended rate
`count()`	`count(Col)`	Non-null row count	Transaction volume, deal count
`max()`	`max(Col)`	Highest value in column	Peak spend, highest ARR account
`min()`	`min(Col)`	Lowest value in column	Floor pricing, minimum margin

All five require a GROUP BY clause if you're grouping — QUERY won't infer it. Unlike SQL, you can't mix aggregated and non-aggregated columns without explicitly grouping the non-aggregated ones.

SUM with GROUP BY: Revenue by Department

This is the workhorse. A P&L tab pulling from a transaction ledger:

=QUERY('Transactions'!A:E,
  "SELECT C, sum(D)
   WHERE B >= date '"&TEXT(Assumptions!$B$3,"yyyy-mm-dd")&"'
   AND B <= date '"&TEXT(Assumptions!$C$3,"yyyy-mm-dd")&"'
   GROUP BY C
   LABEL sum(D) 'Revenue'",1)

Column C is department, column D is amount. The date range is pulled from Assumptions!$B$3:$C$3 so the period is controlled in one place. Change the dates on the Assumptions tab and every QUERY downstream refreshes.

For a board pack pulling $4.2M in Q1 revenue broken by 6 cost centers, this pattern runs in under 2 seconds on a 50,000-row ledger — faster than a pivot table that has to be manually refreshed.

AVG: Blended Rates and Deal Sizes

=QUERY('Pipeline'!A:F,
  "SELECT D, avg(E), count(A)
   WHERE F = 'Closed Won'
   AND C >= date '"&TEXT(TODAY()-90,"yyyy-mm-dd")&"'
   GROUP BY D
   LABEL avg(E) 'Avg Deal Size', count(A) 'Deal Count'",1)

Column D is sales rep, E is ACV, F is stage. This gives you average deal size ($186K in our model) alongside count per rep in the same formula — no helper columns.

One thing avg() won't handle: weighted averages. If you need a revenue-weighted blended rate, you need sum(revenue * rate) / sum(revenue), which QUERY can't express. You'll need SUMPRODUCT for that.

COUNT: Transaction Volume Without Duplicates

=QUERY('GL'!A:G,
  "SELECT E, count(A)
   WHERE D = 'OPEX'
   GROUP BY E
   ORDER BY count(A) DESC
   LABEL count(A) 'Transaction Count'",1)

count() counts non-null values in the specified column. If you want distinct counts (unique vendors, unique accounts), QUERY can't do it natively. The standard workaround is wrapping with COUNTUNIQUE on the output, or using SUMPRODUCT with COUNTIF arrays on the source data instead.

This is arguably the biggest limitation in the aggregate function set. A countd() function would eliminate half the helper-column workarounds that clutter financial models.

MAX and MIN: Bounds Checking

Both are straightforward but get useful in variance analysis:

=QUERY('Monthly_Actuals'!A:D,
  "SELECT B, min(C), max(C), avg(C)
   WHERE A >= date '2025-01-01'
   GROUP BY B
   LABEL min(C) 'Floor', max(C) 'Peak', avg(C) 'Average'",1)

This returns the monthly range for each line item — useful for spotting a cost center that swings from $280K to $410K month to month when it should be flat. Combine max() with min() in the same query and you get spread without writing two separate formulas.

Combining Multiple Aggregates in One Query

QUERY lets you stack multiple aggregate functions in a single SELECT, which is the main reason to reach for it over pivot tables in a model context:

=QUERY('Revenue'!A:F,
  "SELECT B, C,
          sum(D)   as TotalRevenue,
          avg(D)   as AvgDeal,
          count(A) as DealCount,
          max(D)   as LargestDeal
   WHERE E = 'Enterprise'
   GROUP BY B, C
   ORDER BY sum(D) DESC",1)

Column B is quarter, C is region, D is ACV. One formula gives you a complete regional breakdown: $3.1M total, $217K average deal, 14 deals, $890K largest — grouped by quarter and region, sorted by revenue. This would be 4 separate pivot tables otherwise, all requiring manual refresh.

PIVOT: The Aggregate-Plus-Crosstab Pattern

QUERY's PIVOT clause applies aggregate functions and transposes unique values into columns. For a bank syndicate model where you need exposure by counterparty across quarters:

=QUERY('Exposure'!A:E,
  "SELECT B, sum(D)
   WHERE C = 'Drawn'
   GROUP BY B
   PIVOT A
   LABEL sum(D) ''",1)

Column A is quarter (Q1–Q4), B is counterparty, D is exposure amount. PIVOT transposes quarters into columns automatically. The catch: column headers are dynamic, so any formula referencing specific columns downstream will break if the pivot structure changes. For static reporting that goes into a board deck, fine. For a live model with downstream dependencies, use static SUMIFS instead.

What QUERY Can't Do

Worth being explicit about the gaps, since they affect model design decisions:

No countd() (distinct count). Count unique values with =SUMPRODUCT(1/COUNTIF(range,range)) or a helper column.

No median(). Use =MEDIAN(IF(...)) as an array formula instead.

No stdev() or var(). Statistical aggregates aren't in the spec. Pull the data with QUERY and wrap with STDEV() outside.

No window functions. Running totals, rank, and lag/lead don't exist. These require helper columns or SCAN/LAMBDA in newer Sheets versions.

No conditional aggregation. SQL's SUM(CASE WHEN...) pattern doesn't translate. Use SUMIFS across tabs instead — =SUMIFS('P&L'!D:D,'P&L'!C:C,"OPEX",'P&L'!B:B,">="&Assumptions!$B$3).

A Note on LABEL and FORMAT

Both clauses work on aggregated columns. LABEL sum(D) 'Revenue' renames the output header — critical when the default sum D header breaks downstream VLOOKUP references. FORMAT sum(D) '$#,##0' applies number formatting in the query output itself, though this converts the column to text, which breaks any formula that does math on the result. Use FORMAT only for display-only outputs. For anything you'll calculate against, skip FORMAT and apply the cell format separately.

As of April 2026, Google's Sheets documentation specifies: "The LABEL clause sets the label for one or more columns." The clause accepts a string literal and must come after GROUP BY, PIVOT, ORDER BY, and LIMIT in the query string.

If you're pulling large datasets across tabs and want something closer to a full SQL toolkit — joins, window functions, countd() — the DuckDB integration for Google Sheets covers what that looks like in practice.

Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.