How to Calculate Subtotal in Google Sheets

How-to basics · Updated November 22, 2026

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

  1. 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.
  2. 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.
  3. 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).
  4. 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.
  5. 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).
  6. 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.
  7. 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.
  8. 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.
  9. 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 SUBTOTAL function 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 =SUB and 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

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.