SUMIF vs QUERY for Aggregation in Google Sheets (2026)
Home› SUMIF vs QUERY in Google Sheets
Google Sheets Comparison

SUMIF vs QUERY for Aggregation

Both aggregate. Both filter first, then compute. But SUMIF (and its family — SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS) returns a single value in one cell, while QUERY can produce entire summary tables with GROUP BY. The choice matches the shape of your answer — one number or many.

⚡ Quick Answer

Use SUMIF/COUNTIF/AVERAGEIF when you want one aggregate value in one cell — perfect for KPI tiles, single totals, or headline numbers. Use QUERY when you want aggregated results grouped by category — one formula produces a whole summary table instead of dozens of SUMIF cells.

The SUMIF family — one aggregate per cell

SUMIF and its cousins form the traditional Excel-and-Sheets aggregation toolkit:

=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)
=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2, ...)
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, range1, criteria1, ...)

Each returns a single value based on rows that match one or more criteria. Each fills exactly one cell.

Extensive coverage in the SUMIF & SUMIFS complete guide — 8 sheets of examples, common errors, and the dashboard pattern.

SUMIF family strengths

  • One formula, one number — clean for KPI tiles and single totals
  • Familiar syntax — every spreadsheet user knows them
  • Cross-platform — works identically in Excel
  • Fast on all dataset sizes
  • Easy to explain, debug, and modify

SUMIF family weaknesses

  • Need one formula per category — 5 regions × 3 products = 15 SUMIFS
  • Adding a new category requires manually adding a new formula
  • Multiple aggregations (SUM + AVG + COUNT) mean multiple formulas per row
  • Not designed for "give me a summary table" scenarios

QUERY — grouped aggregation

=QUERY(range, "SELECT column, SUM(other_column)
              WHERE conditions
              GROUP BY column
              ORDER BY SUM(other_column) DESC", 1)

QUERY handles grouped aggregation the way SQL does — one formula produces a whole table of subtotals. New categories in your data automatically appear in the output.

QUERY strengths for aggregation

  • One formula produces an entire summary table
  • New categories in source data auto-appear in output
  • Multiple aggregations in one SELECT — SUM, AVG, COUNT, MAX, MIN in the same output
  • Sort and rank inline with ORDER BY
  • Filter with WHERE before aggregating (like SUMIFS but cleaner for complex conditions)

QUERY weaknesses for aggregation

  • Verbose for a single total — SUMIF is much shorter
  • Requires learning SQL-like syntax
  • Output spills into a range, not a single cell — makes some layouts awkward
  • Doesn't work in Excel

Side-by-side comparison

AspectSUMIF familyQUERY
Output shapeSingle cellRange (table)
Syntax familySpreadsheet formulasSQL-like
Learning curveGentleSteeper
One aggregationIdealOverkill
Multiple aggregations across categoriesMany formulas neededOne formula
New categories auto-appearNo — must add formulasYes
Multiple metrics per rowMultiple formulasNative
Sorting outputSeparate stepInline with ORDER BY
Speed for single valueFastestFast
Speed for grouped outputDepends on number of groupsFast
Excel compatibilityUniversalSheets only
Best forKPI tiles, headline numbersSummary tables, dashboards

Same problem, both approaches

Total sales for North region

SUMIF — clear winner
=SUMIF(Sales!C:C, "North",
       Sales!E:E)

One line, one number.

QUERY — overkill
=QUERY(Sales!A:E,
  "SELECT SUM(E) WHERE C='North'
   LABEL SUM(E) ''", 1)

Works but verbose.

Winner: SUMIF. Single value = single-cell formula.

Total sales by region (all regions)

SUMIF — brittle
North:  =SUMIF(C:C, "North", E:E)
South:  =SUMIF(C:C, "South", E:E)
East:   =SUMIF(C:C, "East", E:E)
West:   =SUMIF(C:C, "West", E:E)

4 formulas. If someone adds a "Central" region to data, you have to add another formula.

QUERY — one shot
=QUERY(Sales!A:E,
  "SELECT C, SUM(E)
   GROUP BY C
   ORDER BY SUM(E) DESC", 1)

One formula. New regions auto-appear.

Winner: QUERY. Grouped output = QUERY territory.

Region × Product cross-tab

SUMIFS — many formulas
-- One SUMIFS per cell in the grid:
=SUMIFS(E:E, C:C, "North", D:D, "Widget")
=SUMIFS(E:E, C:C, "North", D:D, "Gadget")
=SUMIFS(E:E, C:C, "South", D:D, "Widget")
-- ...and so on for every combination.

Anchor references, drag across a grid. Works but rigid.

QUERY with 2 GROUP BYs
=QUERY(Sales!A:E,
  "SELECT C, D, SUM(E)
   GROUP BY C, D
   ORDER BY C, D", 1)

All combinations in one output.

Winner: Depends on layout. For a proper cross-tab grid (rows × cols), Pivot Table or SUMIFS with drag. For a long-format table, QUERY.

Total sales AND average sales AND count by region

SUMIF family — three columns
Region | Sum | Avg | Count
North  | =SUMIF | =AVERAGEIF | =COUNTIF
South  | =SUMIF | =AVERAGEIF | =COUNTIF
East   | =SUMIF | =AVERAGEIF | =COUNTIF

Three formulas per region. Adds up fast.

QUERY — one shot
=QUERY(Sales!A:E,
  "SELECT C, SUM(E), AVG(E), COUNT(E)
   GROUP BY C", 1)

Three metrics × N regions in one formula.

Winner: QUERY. Multiple aggregations across groups is where QUERY dominates.

Decision framework

Two questions determine which to use:

1. Is the output one value or many?

  • One value → SUMIF/COUNTIF/AVERAGEIF
  • Many values (a table) → QUERY

2. Do you need to group by a category?

  • No, just one filtered aggregate → SUMIF family
  • Yes, grouped by one or more categories → QUERY

These usually align. Single value = no grouping = SUMIF. Multiple values = grouping = QUERY.

Using both together

Real dashboards mix them. Common pattern:

Top of dashboard — KPI tiles (SUMIF/COUNTIF territory):

Total Sales:    =SUM(Sales!E:E)
North Region:   =SUMIF(Sales!C:C, "North", Sales!E:E)
Widget Sales:   =SUMIF(Sales!D:D, "Widget", Sales!E:E)
Order Count:    =COUNTA(Sales!A:A) - 1

Middle of dashboard — grouped breakdowns (QUERY territory):

=QUERY(Sales!A:E,
  "SELECT C, SUM(E), AVG(E), COUNT(E)
   GROUP BY C
   ORDER BY SUM(E) DESC", 1)

Bottom of dashboard — filtered detail lists (QUERY or FILTER):

=QUERY(Sales!A:E,
  "SELECT * WHERE E > 500
   ORDER BY A DESC", 1)
Match the tool to the section

Headline KPIs at the top = SUMIF (fastest, cleanest). Grouped tables in the middle = QUERY (one formula per table). Detail lists at the bottom = QUERY or FILTER. This layered pattern keeps dashboards maintainable — non-technical users can update the SUMIF numbers easily, and technical users own the QUERY grids.

The verdict

🎯 Bottom line

SUMIF family for single values. KPI tiles, headline totals, filtered counts. Simple, fast, cross-platform-friendly. If your output is one number in one cell, don't reach for QUERY.

QUERY for grouped aggregation. Summary tables, region-by-product breakdowns, multi-metric dashboards. One QUERY replaces dozens of SUMIFS while staying resilient to new categories.

Learn both — they cover different sections of the same dashboard. Real workbooks use SUMIF for the top and QUERY for the middle. Combining them thoughtfully is the mark of a mature Sheets user.

Frequently asked questions

Should I use SUMIF or QUERY for totals in Google Sheets?

Use SUMIF for a single total that appears in one cell — like "total sales for North region." Use QUERY when you need multiple grouped totals — like "total sales by region for every region." SUMIF gives you one number; QUERY gives you a summary table. Match the tool to the shape of the output you want.

Is QUERY faster than SUMIF for aggregation?

For a single aggregation, SUMIF is faster because it's lightweight. For multiple aggregations across categories, QUERY is faster because it does the grouping in one pass instead of forcing you to write dozens of SUMIF formulas. For dashboards with many KPIs, mixing the two is common — SUMIF for the headline numbers, QUERY for grouped breakdowns.

Can QUERY do everything SUMIF does?

Yes, but with more overhead. QUERY can replicate any SUMIF or SUMIFS call using SELECT SUM() WHERE. The trade-off is verbosity — SUMIF for a single total is 20 characters; the equivalent QUERY is 40+. For simple single-cell aggregations, SUMIF is more concise. Where QUERY wins is when you need grouped or multi-metric aggregation.

When is COUNTIF better than QUERY COUNT?

For a single count in a specific cell, COUNTIF is simpler: =COUNTIF(A:A, "North") vs =QUERY(A:A, "SELECT COUNT(A) WHERE A='North'"). The QUERY version also requires unpacking the result. For a table of counts by category, QUERY wins: one QUERY replaces dozens of COUNTIFs.

Do SUMIF and QUERY work in Excel?

SUMIF, COUNTIF, AVERAGEIF (and their -IFS versions) work identically in Excel and Google Sheets. QUERY is Google Sheets-exclusive — Excel has no direct equivalent. Cross-platform users should master SUMIF/COUNTIF; QUERY is a Sheets specialty.