To subtotal in Google Sheets, you use the SUBTOTAL function, which lets you sum, average, count, or apply other functions to a range while optionally ignoring hidden rows. The basic syntax is =SUBTOTAL(function_code, range1, [range2, ...]). For example, to sum values in B2:B20, you’d use =SUBTOTAL(9, B2:B20). This guide walks you through using it, including how to create subtotals with the built-in menu, and covers common pitfalls and troubleshooting.
What You’ll Need
- A Google account and a Google Sheets document with your data.
- Your data should be organized in columns with headers in the first row (e.g., Category, Amount).
- If you want subtotals by category, sort your data by that category first (Data > Sort sheet by column).
Step-by-Step
- Open your spreadsheet in Google Sheets (sheets.google.com).
- Review your data. Ensure your data is in a table with headers. For example, column A might be “Category” and column B “Amount”.
- Sort your data by the column you want to subtotal by (if needed). Select the entire data range (including headers), go to Data > Sort sheet by column A → Z (or Z → A). This groups similar items together.
- Use the SUBTOTAL function manually (most flexible method):
- Click an empty cell where you want the subtotal (e.g., below a group of rows).
- Type
=SUBTOTAL(9, B2:B5)to sum the amounts in rows 2 through 5. The code9means SUM. Other common codes: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 10=VAR, 11=VARP. For ignoring manually hidden rows, use codes 101-111 (e.g., 109 for SUM ignoring hidden). - Press Enter. The subtotal appears.
- Use the Data > Subtotal menu (for automatic subtotals):
- Make sure your data is sorted by the column you want to group by.
- Select any cell within your data range.
- Go to Data > Subtotal (in the menu bar).
- In the dialog, set “At each change in” to the column you want to group by (e.g., Category).
- Set “Use function” to SUM (or another function).
- Check the box next to the column(s) you want to subtotal (e.g., Amount).
- Click OK. Google Sheets inserts subtotal rows with the SUBTOTAL function and a grand total at the bottom.
- Add multiple subtotal levels (if needed): Use the Data > Subtotal dialog again, but before clicking OK, check “Replace current subtotals” if you want to add another grouping level. For example, first subtotal by Region, then by Category within each region. You must sort by both columns first (e.g., sort by Region, then by Category).
- Use SUBTOTAL with filters: If you apply a filter (Data > Create a filter), the SUBTOTAL function with codes 1-11 will ignore rows hidden by the filter, but not rows manually hidden. Codes 101-111 ignore all hidden rows.
Common Mistakes
- Not sorting data before using Data > Subtotal: If your data isn’t sorted by the grouping column, subtotals will appear for each change in value, creating multiple subtotals for the same group scattered throughout.
- Using SUM instead of SUBTOTAL: If you use
=SUMon a range that already contains subtotal rows, you’ll double-count. Always useSUBTOTALfor subtotal rows, and for grand totals, sum the subtotal cells or use=SUBTOTAL(9, range)that includes subtotal rows (SUBTOTAL ignores other SUBTOTALs). - Incorrect function code: Using
1(AVERAGE) when you want a sum will give wrong results. Double-check the code. - Referencing the wrong range: Including the header row or extra blank rows in the SUBTOTAL range can produce errors or incorrect counts.
- Using SUBTOTAL with filtered data but expecting it to ignore manual hidden rows: Codes 1-11 ignore filtered-out rows, but not rows you manually hide. Use 101-111 for that.
Tips
- Use the SUBTOTAL function for dynamic totals: When you filter your data, SUBTOTAL automatically recalculates to show only visible rows (with codes 1-11). This is great for dashboards.
- Create subtotals with the menu, then edit the formulas: The Data > Subtotal feature inserts formulas you can modify. For instance, change the function code from 9 to 109 to ignore manually hidden rows.
- Use keyboard shortcuts: To quickly insert a SUBTOTAL formula, type
=suband press Tab when SUBTOTAL is highlighted. On Windows, Ctrl+Shift+L toggles filters; on Mac, Cmd+Shift+L. - Combine with other functions: You can nest SUBTOTAL in IF statements, but be careful—SUBTOTAL cannot be used inside array formulas like ARRAYFORMULA directly.
- For grand totals, use
=SUBTOTAL(9, B2:B20)even if it includes subtotal rows—SUBTOTAL ignores other SUBTOTAL formulas, so it won’t double-count.
Troubleshooting
- Subtotals are wrong or duplicated: Check if your data is sorted correctly. If you used Data > Subtotal, re-sort and re-apply. If you used manual formulas, ensure each subtotal range doesn’t overlap with another subtotal’s range.
- #VALUE! error: This usually means the function code is invalid (must be between 1-11 or 101-111) or you have text in the range. Make sure the range contains numbers only.
- #REF! error: Occurs when a referenced cell is deleted. Check your formula references and adjust.
- Subtotals don’t update when filtering: If you used codes 101-111, they ignore manually hidden rows but not filter-hidden rows? Actually, codes 101-111 ignore all hidden rows, including filtered ones. If you want to ignore only filtered rows (not manually hidden), use codes 1-11. If you used the menu, it uses code 9 (sum) which ignores filtered rows. If you manually hid rows, they are still counted with code 9. To ignore manual hidden, change to 109.
- Data > Subtotal option is greyed out: This happens if your selection includes multiple sheets or if the range isn’t a contiguous block. Select a single cell within the data range and try again.
- Subtotals not showing after clicking OK: Make sure you have at least one column checked in the “Add subtotal to” list. Also, check if your data has blank rows—those can break the grouping.
Frequently Asked Questions
Q: What is the difference between SUBTOTAL and SUM?
A: SUM adds all numbers in a range, including hidden rows. SUBTOTAL can ignore hidden rows (based on the function code) and ignores other SUBTOTAL formulas, preventing double-counting when you have subtotals within a grand total.
Q: How do I subtotal by multiple columns?
A: Use the Data > Subtotal dialog. First, sort by the primary column, then the secondary column. In the dialog, set “At each change in” to the primary column, then check “Replace current subtotals” and set the secondary column for the second level. You can repeat for more levels.
Q: Can I use SUBTOTAL with AVERAGE?
A: Yes, use function code 1 (or 101 to ignore manually hidden rows). For example, =SUBTOTAL(1, B2:B20) gives the average of visible cells.
Q: Does SUBTOTAL work with text data?
A: SUBTOTAL is designed for numeric data. For counting text, use code 3 (COUNTA) which counts non-empty cells, but it won’t work with SUM on text.
Q: How do I remove subtotals?
A: If you used Data > Subtotal, go to Data > Subtotal and click “Remove all”. If you used manual formulas, simply delete the rows containing the SUBTOTAL formulas.
Q: Why does my SUBTOTAL show 0?
A: Check that the range contains numeric values and that no cells are truly blank or text. Also, ensure the function code is correct—code 9 sums, but if your data is empty, it returns 0.
Related
- US Balance of Trade Data: Latest Figures & Trends
- PySpark partitionBy Multiple Columns: How-To
- Can Traffic Light Cameras Detect Speed? Facts
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 Double Space in Word 365: Text and ParagraphsTo double-space text in Word 365 on Windows, select the text—or press Ctrl+A to select the document—then choose…
- Does Excel Have Box Plots? How to Create OneYes. Microsoft Excel for Microsoft 365 includes a built-in Box and Whisker chart, which is Excel’s name for…
- Excel Day Today: Find Today’s Date and Day NumberToday is Monday, November 2, 2026. In Excel for Microsoft 365, enter =TODAY() in a cell to display…
- Google Sheets Spreadsheet: What It Is and How to Use ItGoogle Sheets is Google’s web-based spreadsheet app: you can enter information in a grid, calculate values with formulas,…
- Track Changes in Word for the Web: A Step-by-Step GuideTo track edits in Word for the web, open the document, select Review > Track Changes, and choose…
- How to Delete a Page in the Middle of a Word DocumentTo delete a page in the middle of a Word document, place the cursor on that page, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.