To calculate a subtotal in Google Sheets, use the SUBTOTAL function. It lets you sum, average, count, or perform other calculations while optionally ignoring hidden rows. The basic syntax is =SUBTOTAL(function_code, range). For example, =SUBTOTAL(9, A2:A10) sums the values in A2:A10. This guide covers the function, its codes, how to use it with filters, and common pitfalls.
What You’ll Need
- A Google Sheets document with numeric data in a column or row.
- Basic familiarity with entering formulas in a cell.
- Optional: a filter view or manual row hiding if you want to exclude rows.
Step-by-Step
- Open your Google Sheet in a web browser. Ensure your data is organized, for example, with numbers in column A from row 2 to row 10.
- Select the cell where you want the subtotal to appear. This is typically below the data column or to the right of a row of numbers.
- Type the SUBTOTAL formula. Start with an equals sign, then
SUBTOTAL, an opening parenthesis, the function code, a comma, the range, and a closing parenthesis. For a sum, use code 9. Example:=SUBTOTAL(9, A2:A10). - Press Enter. The subtotal appears. For the example range A2:A10 containing 10, 20, 30, 40, 50, 60, 70, 80, 90, the result is 450.
- To use a different function, change the code. Code 1 is AVERAGE, 2 is COUNT, 3 is COUNTA, 4 is MAX, 5 is MIN, 6 is PRODUCT, 7 is STDEV, 8 is STDEVP, 9 is SUM, 10 is VAR, and 11 is VARP. For example,
=SUBTOTAL(1, A2:A10)gives 50 (average of 10-90). - To ignore hidden rows, use codes 101-111 instead of 1-11. For example,
=SUBTOTAL(109, A2:A10)sums only visible rows. If you manually hide rows 3 and 5, the sum excludes those values. - To use with filters, apply a filter to your data (Data > Create a filter). Then use codes 101-111. The subtotal automatically recalculates to include only rows that pass the filter. For instance, if you filter to show only rows where column B equals “Yes”,
=SUBTOTAL(109, A2:A10)sums only those visible rows. - To add multiple subtotals (e.g., sum and count), place separate SUBTOTAL formulas in different cells. Example:
=SUBTOTAL(109, A2:A10)for sum and=SUBTOTAL(103, A2:A10)for count of non-empty visible cells. - To subtotal by category, use the SUBTOTAL function within a pivot table or use the Data > Subtotal menu? Actually, Google Sheets does not have a built-in Subtotal menu like Excel. Instead, use the
SUBTOTALfunction manually or create a pivot table (Data > Pivot table) and set values to SUM, which automatically ignores filtered rows.
Common Mistakes
- Using wrong function code: Code 9 sums, but code 109 also sums but ignores hidden rows. Mixing them up gives unexpected results when rows are hidden.
- Including the subtotal cell in the range: If your range includes the cell with the formula itself, it causes a circular reference error. Keep the range strictly to data cells.
- Using SUM instead of SUBTOTAL when you need to ignore hidden rows:
=SUM(A2:A10)always includes hidden rows. Use=SUBTOTAL(109, A2:A10)to exclude them. - Forgetting that SUBTOTAL ignores other SUBTOTAL formulas: If your range contains other SUBTOTAL functions, they are ignored to avoid double-counting. This is usually desired but can surprise you.
- Not updating the range when adding data: If you add rows, the range in the formula won’t automatically expand. Edit the formula or use a dynamic range like
A2:A(entire column) if safe.
Tips
- Use named ranges for clarity: Select the data, go to Data > Named ranges, name it e.g., “SalesData”, then use
=SUBTOTAL(109, SalesData). - Combine with IF for conditional subtotals: For example, to sum only values over 50, use
=SUBTOTAL(109, FILTER(A2:A10, A2:A10>50)). Note that FILTER works with dynamic arrays; this formula sums the filtered result. - Use the function picker: Type
=SUBand Google Sheets suggests SUBTOTAL. Click it to see the function helper with codes. - For quick subtotals in a pivot table: Pivot tables automatically ignore filtered rows and give subtotals per group. This is more scalable for large datasets.
- Keyboard shortcut for sum: On Windows, Alt+Shift+= inserts a SUM formula, but not SUBTOTAL. For SUBTOTAL, type it manually.
Troubleshooting
- Formula shows #NAME?: This means you misspelled SUBTOTAL or used a non-English locale. Check spelling and ensure commas are used as argument separators (in some locales, semicolons are used).
- Formula shows #VALUE!: The range contains text or errors. SUBTOTAL ignores text in numeric functions, but if the range includes error cells, it returns an error. Remove or fix errors.
- Result does not change when hiding rows: You used code 9 instead of 109. Change to 109.
- Result includes hidden rows when using a filter: Ensure you use codes 101-111. Also, make sure the filter is actually applied (the filter icon is active).
- Circular reference error: The range includes the formula cell. Adjust the range to exclude it.
- Subtotal not updating automatically: Google Sheets recalculates automatically, but if you have iterative calculation off and a circular reference, it may not. Check for circular references and fix them.
Frequently Asked Questions
Q: What is the difference between SUBTOTAL and SUM?
A: SUM adds all cells in a range, including hidden rows. SUBTOTAL can sum while ignoring hidden rows (using codes 101-111) and also offers other functions like average and count.
Q: Can I use SUBTOTAL with multiple ranges?
A: Yes, you can list multiple ranges separated by commas, like =SUBTOTAL(9, A2:A10, C2:C10). It sums all values across those ranges.
Q: How do I get subtotals for each category automatically?
A: Use a pivot table. Select your data, go to Data > Pivot table, choose rows for the category, and values as SUM of the numeric column. Pivot tables automatically show subtotals for each group and ignore filters.
Q: Does SUBTOTAL work in Excel the same way?
A: Yes, the SUBTOTAL function exists in Excel with the same codes. However, Excel has a Subtotal menu (Data > Subtotal) that inserts subtotal rows automatically, which Google Sheets lacks.
Q: Why does my SUBTOTAL formula return 0?
A: The range may be empty or contain only text. Check that the range has numeric values. Also, if you use code 9 but the cells are formatted as text, it returns 0. Convert text to numbers.
Q: Can I nest SUBTOTAL inside another function?
A: Yes, you can use SUBTOTAL as an argument, e.g., =IF(SUBTOTAL(109, A2:A10)>100, "High", "Low"). But note that SUBTOTAL ignores other SUBTOTAL formulas in its range.
Related
- Excel Spreadsheet Zoom Lock: How to Freeze Zoom Level
- Excel Staff Schedule Template: Free Options & How to Build One
- Excel Spreadsheet Templates for Tracking Time
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create Plain Labels in Word Without a TemplateTo create a plain sheet of blank labels in Microsoft Word, open Mailings > Labels, choose the product…
- Flash Fill Practice Sheet: Excel Drills and AnswersTo practice Flash Fill in Excel for Microsoft 365 on Windows, put source data beside an empty column,…
- Undo in Excel: Shortcuts, Limits, and Recovery TipsIn Excel for Microsoft 365 on Windows, press Ctrl+Z to undo your most recent action. On a Mac,…
- Counter Class in Python: Usage & ExamplesThe Counter class in Python is a specialized dictionary from the collections module that counts hashable objects, making…
- How to Double Space in Word on iPadTo double-space text in Word on your iPad, select the text, open the Home tab, tap Line Spacing,…
- How to Find Duplicate Entries in ExcelTo find duplicate entries in Excel, select the cells you want to check, then choose Home > Conditional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.