SUBTOTAL function
Quick answer
SUBTOTAL calculates a sum, average, count or other total for a range while skipping any other SUBTOTAL formulas inside it, so section subtotals and a grand total never double count.
- Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)- Most common use
=SUBTOTAL(9,B2:B3)returns $205 (Paper and toner)
It always leaves out rows hidden by a filter, and with function numbers 101–111 it also leaves out rows you hid by hand, which makes it the total to use under a filtered list.
- Available in
- Every current version
- Category
- Math functions
- Returns
- The result of the chosen calculation over the visible cells, without other subtotals.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
| Argument | Required? | What it is |
|---|---|---|
function_num | Required | Which calculation: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEV.P, 9 SUM, 10 VAR, 11 VAR.P. Add 100 to skip hidden rows. |
ref1 | Required | The first range to total. |
ref2 | Optional | Optional: more ranges. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B4 | =SUBTOTAL(9,B2:B3) | $205 | Paper and toner |
| B7 | =SUBTOTAL(9,B5:B6) | $950 | Chairs and desks |
| B8 | =SUBTOTAL(9,B2:B7) | $1,155 | Every item once: the two subtotals inside the range are skipped |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Item | Amount |
| Paper | $120 |
| Toner | $85 |
| Supplies subtotal | $205 |
| Chairs | $340 |
| Desks | $610 |
| Furniture subtotal | $950 |
| Grand total | $1,155 |
Practice SUBTOTAL
Graded instantly, then re-checked on changed data.
Totals without double counting
B4, the subtotal for software in B8, and a grand total in B9 that counts every expense once. Use SUBTOTAL for all three so the grand total skips the subtotals.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The grand total is double | SUM over a range that already holds subtotals counts them again. Use SUBTOTAL for every total and the grand total skips the others. |
| Hidden rows still count | Numbers 1–11 include rows hidden by hand. Use 101–111, such as SUBTOTAL(109,B2:B20), to skip them. Rows hidden by a filter are always skipped. |
| #VALUE! | The function number isn't 1–11 or 101–111, or the argument is an array rather than a range. |
Tips
- Add a total row under a filtered list with =SUBTOTAL(109,B2:B500): it changes as you filter.
Questions
What does 9 mean in SUBTOTAL(9, …)?
It picks SUM. 1 is AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX and 5 MIN; 101–111 are the same but also skip rows hidden by hand.
How do I sum only visible rows after filtering?
=SUBTOTAL(9,B2:B100). Rows hidden by a filter are always left out; use 109 to leave out rows hidden by hand too.
How is SUBTOTAL different from AGGREGATE?
AGGREGATE has more calculations, such as MEDIAN, LARGE and percentiles, and can also skip error values.
Updated Checked by running: every formula on this page was run by our formula engine on .