SUBTOTAL performs calculations like SUM, AVERAGE, or COUNT on a range while automatically ignoring other SUBTOTAL results and, optionally, hidden rows. It is ideal for totals in filtered or grouped lists where nested subtotals would otherwise be double-counted.
=SUBTOTAL(function_num, ref1, [ref2], …)What does SUBTOTAL do?
The SUBTOTAL function is a versatile tool that calculates a summary statistic—such as a sum, average, or count—for a range of cells. What makes it special is its ability to ignore other SUBTOTAL formulas within that range, preventing double-counting when you have subtotals inside a larger list. It can also skip rows that are hidden by a filter, giving you accurate results for the visible data only.
This matters because ordinary functions like SUM or AVERAGE include every value in the range, even those from nested subtotals or hidden rows. That can lead to inflated totals or misleading averages. SUBTOTAL solves this by design: it recognises and excludes other SUBTOTAL results, and with the right function number, it ignores manually hidden rows as well.
Imagine you manage a sales report with regional subtotals and a grand total at the bottom. If you use SUM for the grand total, it will add the regional subtotals and the individual sales, doubling the numbers. With SUBTOTAL, the grand total correctly adds only the individual sales, because it skips the regional subtotal rows. Similarly, when you filter the list, SUBTOTAL recalculates to show the total for the visible rows only.
When to use SUBTOTAL
- Calculating a grand total in a list that already contains subtotals for groups.
- Getting sums or averages that automatically adjust when rows are filtered.
- Building reports where users may hide rows manually and expect totals to reflect only visible data.
- Creating dynamic dashboards that need to ignore nested summary rows.
- Counting visible entries in a filtered table without using helper columns.
SUBTOTAL syntax
=SUBTOTAL(function_num, ref1, [ref2], …)| Argument | Required? | What to enter |
|---|---|---|
function_num | Required | A number from 1 to 11 or 101 to 111 that specifies which function to use (e.g., 9 for SUM, 1 for AVERAGE, 2 for COUNT). Numbers 101–111 also ignore manually hidden rows. |
ref1 | Required | The first range or reference for which you want the subtotal. |
ref2 | Optional | Additional ranges or references (up to 254) to include in the subtotal. |
What it returns: A number that is the result of the chosen function (e.g., sum, average, count) applied to the referenced cells, excluding other SUBTOTAL formulas and, for function numbers 101–111, manually hidden rows.
SUBTOTAL examples
1 Basic sum of a single range
This example shows the simplest use: summing a list of monthly sales. The formula =SUBTOTAL(9, B2:B6) uses function number 9, which stands for SUM. It adds the values in B2 through B6, ignoring any other SUBTOTAL formulas (there are none here). The result is 6700, the total of the five monthly figures.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | |||
| 2 | Jan | 1200 | 6700 | ||
| 3 | Feb | 1500 | |||
| 4 | Mar | 1100 | |||
| 5 | Apr | 1700 | |||
| 6 | May | 1200 | |||
| 7 |
2 Average with function number 1
Here we calculate the average order value for a small set of orders. The formula =SUBTOTAL(1, C2:C5) uses function number 1, which stands for AVERAGE. It averages the values in C2 through C5, ignoring any other SUBTOTAL formulas. The total of the four orders is 1000, so the average is 250.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order ID | Customer | Amount | |||
| 2 | 1001 | Acme | 200 | 250 | ||
| 3 | 1002 | Beta | 300 | |||
| 4 | 1003 | Gamma | 150 | |||
| 5 | 1004 | Delta | 350 | |||
| 6 |
Common SUBTOTAL errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The function_num argument is not a number or is outside the valid range (1–11 or 101–111). | Use a valid function number, such as 9 for SUM or 1 for AVERAGE. |
| #REF! | A referenced range has been deleted or moved, leaving an invalid reference. | Update the formula to refer to existing cells or ranges. |
| Incorrect total (double-counting) | Using a regular function like SUM instead of SUBTOTAL, so nested subtotals are included. | Replace SUM with SUBTOTAL and use the appropriate function number (e.g., 9 for SUM). |
| Hidden rows still included | Using function numbers 1–11, which do not ignore manually hidden rows. | Use function numbers 101–111 to ignore manually hidden rows. |
Tips for using SUBTOTAL
- Use function numbers 101–111 when you want totals to reflect only visible rows after manual hiding.
- SUBTOTAL automatically ignores other SUBTOTAL formulas, so you can safely nest subtotals within a larger range.
- For filtered lists, SUBTOTAL with any function number ignores rows hidden by filters; the 101–111 range additionally ignores manually hidden rows.
- Combine SUBTOTAL with Excel Tables (Ctrl+T) for dynamic ranges that expand automatically.
- Remember that SUBTOTAL does not include cells with errors; if a referenced cell contains an error, the result will be an error.
Frequently asked questions
What is the difference between SUBTOTAL and SUM?
SUM adds all numbers in a range, including those from nested subtotals. SUBTOTAL adds numbers but ignores other SUBTOTAL results, preventing double-counting. SUBTOTAL can also ignore hidden rows when using function numbers 101–111.
Why does SUBTOTAL return a different result after filtering?
SUBTOTAL is designed to ignore rows hidden by filters (and manually hidden rows if you use 101–111). So when you filter, the function recalculates using only the visible rows.
Can SUBTOTAL use functions other than SUM?
Yes. The function_num argument lets you choose from 11 functions, including AVERAGE, COUNT, MAX, MIN, and more. Use 1–11 for standard behaviour or 101–111 to also ignore manually hidden rows.
Does SUBTOTAL work with horizontal ranges?
Yes, SUBTOTAL works with any range, whether vertical or horizontal, as long as you provide valid references.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SUBTOTAL reference