Google Sheets Total Formula: Master Totals in Sheets
A practical guide to the google sheets total formula, covering SUM, SUMIF, SUMIFS, SUBTOTAL, and QUERY to compute totals across datasets while handling hidden rows and categories.
Totals in Google Sheets are computed with a family of functions rather than a single formula. This quick answer introduces core patterns using SUM, SUMIF, SUMIFS, SUBTOTAL, and QUERY to produce column totals, row totals, and category totals, including handling visible vs hidden rows and conditional aggregations. This overview also flags when to use each function and points to common pitfalls with data types and ranges.
Understanding totals in Google Sheets
Totals are a fundamental part of data analysis in Google Sheets. The google sheets total formula family isn't a single switch you turn on; it's a toolkit you combine. Common goals include summing a single column, computing a sum with conditions, and producing category totals that you can feed into dashboards. The group of functions in this toolkit includes SUM for straightforward totals, SUMIF and SUMIFS for conditional totals, SUBTOTAL and AGGREGATE to respect or ignore hidden rows, and QUERY for grouped sums. According to How To Sheets, choosing the right pattern is about data shape (flat lists vs. grouped data) and how you want to present results (single numbers vs. grouped summaries). In practice, start with a quick baseline sum to sanity-check your data; then layer on conditions or grouping as needed.
=SUM(A2:A100)Explanation: This simple formula adds all numeric values in A2:A100. If any cells contain text, they are ignored. For dynamic sheets, you may use A:A to sum the entire column, but that can impact performance on large datasets.
=SUBTOTAL(9, A2:A100)Explanation: SUBTOTAL respects filtering; if some rows are hidden, SUBTOTAL(9, range) will include only visible rows when used with the right function_num. If you want to ignore manually hidden rows, you can use 109 as the function_num.
Core totals: SUM, SUMIF, SUMIFS
The core building blocks for totals are the SUM family. Use SUM for straightforward column totals, SUMIF for single-criterion totals, and SUMIFS for multiple criteria. Think about the data layout: if you have a list of sales per product and a numeric amount column, these formulas let you compute per-product totals efficiently.
=SUM(B2:B100)=SUMIF(A2:A100, \"Apples\", B2:B100)=SUMIFS(B2:B100, A2:A100, \"Apples\", C2:C100, \">2026-01-01\")Explanation: The first sums all values in B; the second sums B where A equals Apples; the third adds a date condition to limit the period. These patterns scale to larger datasets and multiple criteria.
Handling hidden rows and filters with SUBTOTAL and AGGREGATE
When your data is filtered, standard sums may misrepresent totals. SUBTOTAL is designed for this scenario: function_num 9 = SUM sums visible rows only under filters. AGGREGATE provides even more control with options to ignore hidden rows and errors, while still returning a numeric total that fits dashboards.
=SUBTOTAL(9, B2:B100)=AGGREGATE(9, 5, B2:B100)Explanation: SUBTOTAL respects filter visibility, while AGGREGATE with the ignore_hidden option can be tuned for mixed data. These functions are essential for accurate totals in reports and PDFs.
Grouped totals with QUERY and ARRAYFORMULA
For category-level totals, QUERY is a powerful option. It can group rows by a category and return sums in a compact, report-ready table. ARRAYFORMULA can extend simple formulas to whole ranges where appropriate, reducing the need for manual dragging and enabling dynamic dashboards.
=QUERY(A1:C, \"SELECT A, SUM(B) WHERE A IS NOT NULL GROUP BY A\", 1)=ARRAYFORMULA(QUERY(A1:C, \"SELECT A, SUM(B) WHERE A IS NOT NULL GROUP BY A\", 1))Explanation: The first query returns one row per category with the total amount. The ARRAYFORMULA variant helps propagate the result across adjacent cells when your data changes frequently.
Practical end-to-end example
Consider a sheet with a small transaction dataset: Category, Amount, Date. This block shows how to total Apples and group by category using real formulas, including a sample mini dataset and results. The formulas below are designed to be pasted into a Google Sheets cell with the data layout shown.
Category,Amount,Date
Apples,12,2026-01-01
Oranges,7,2026-01-02
Apples,5,2026-01-03
Bananas,8,2026-01-04
=SUMIF(A2:A5, \"Apples\", B2:B5)For a category-wide view:
=QUERY(A1:C5, \"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A\", 1)After filtering to a subset, you can use:
=SUBTOTAL(9, B2:B5)Explanation: The data and formulas illustrate a simple end-to-end approach from raw rows to grouped totals and filtered totals. This pattern scales to larger datasets while remaining readable and auditable.
Common pitfalls and performance considerations
As datasets grow, the choice of range size and function can impact performance. Prefer defined ranges (A2:A1000) over full-column references (A:A) when possible, especially in dashboards. Ensure numeric columns don't contain stray text; otherwise, sums will miscount. When combining multiple functions (SUM+FILTER+QUERY), keep formulas readable by using named ranges and breaking long expressions into helper columns. Finally, test formulas against manual calculations to confirm accuracy.
=SUM(A2:A1000)=QUERY(A1:C, \"SELECT A, SUM(B) WHERE B > 0 GROUP BY A\", 1)Explanation: Clear, constrained ranges speed up calculation and reduce error surface. Use QUERY for group-level totals and SUBTOTAL/AGGREGATE for filtered totals to maintain accuracy across views.
Steps
Estimated time: 15-25 minutes
- 1
Prepare your data
Organize a clean table with headers for Category, Amount, Date. Ensure Amount is numeric and dates are properly formatted. This makes totals reliable and easier to audit.
Tip: Use named ranges for stable references across sheets. - 2
Choose a total method
Decide whether you need a simple total, a conditional total, or category totals. This choice affects which function you’ll use.
Tip: SUM is fast for simple totals; SUMIF/SUMIFS add conditions. - 3
Compute a baseline total
Start with a basic total to verify your data range. This helps you confirm the range contains numeric values only.
Tip: Test with a smaller dataset to validate results. - 4
Add conditional totals
Add SUMIF or SUMIFS to compute totals by criteria, such as by category or by date.
Tip: Use absolute references for ranges when copying formulas. - 5
Group totals with QUERY
Leverage QUERY to return grouped sums by category, enabling quick dashboards.
Tip: Quotes in QUERY formulas must be escaped properly. - 6
Validate and automate
Cross-check totals with alternative formulas and consider ARRAYFORMULA for scalable calculations.
Tip: Avoid recalculating large ranges in every cell.
Prerequisites
Required
- Required
- Required
- Familiarity with basic spreadsheet formulas (SUM, IF, etc.)Required
Optional
- Optional: Google Apps Script for automationOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy a formula | Ctrl+C |
| PastePaste into cells | Ctrl+V |
| UndoRevert last action | Ctrl+Z |
| RedoRedo last action | Ctrl+Y |
FAQ
What is the best way to total a column with hidden rows?
For totals that respect hidden rows, use SUBTOTAL with function_num 9 for sum, or AGGREGATE with the ignore_hidden option. These approaches adapt to filtered data.
Use SUBTOTAL or AGGREGATE to total data while respecting hidden rows, especially after filters.
How do I total values conditionally in Google Sheets?
SUMIF handles single condition totals, while SUMIFS supports multiple criteria. Specify ranges and criteria to compute the correct total.
Use SUMIF for one condition or SUMIFS for multiple criteria.
Can I total by category without a pivot table?
Yes. Use QUERY to group and sum by category, or use SUMIF/SUMIFS with a lookup table. These methods avoid a pivot table.
Yes, you can total by category using QUERY or SUMIF/SUMIFS.
Is there a performance concern with huge data ranges?
Yes. Large full-column ranges can slow sheets. Prefer defined ranges or dynamic filtering to limit input.
Yes, avoid very large ranges when possible to keep sheets fast.
The Essentials
- Use SUM for base totals
- SUMIF/SUMIFS handle conditional totals
- SUBTOTAL/AGGREGATE respect hidden rows
- QUERY provides grouped totals by category
- The How To Sheets team recommends validating totals with multiple approaches