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.
📋 What's in this comparison
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
| Aspect | SUMIF family | QUERY |
|---|---|---|
| Output shape | Single cell | Range (table) |
| Syntax family | Spreadsheet formulas | SQL-like |
| Learning curve | Gentle | Steeper |
| One aggregation | Ideal | Overkill |
| Multiple aggregations across categories | Many formulas needed | One formula |
| New categories auto-appear | No — must add formulas | Yes |
| Multiple metrics per row | Multiple formulas | Native |
| Sorting output | Separate step | Inline with ORDER BY |
| Speed for single value | Fastest | Fast |
| Speed for grouped output | Depends on number of groups | Fast |
| Excel compatibility | Universal | Sheets only |
| Best for | KPI tiles, headline numbers | Summary 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)
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.