Math & Trig
All versions
◉ 60 views
=SUBTOTAL()
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=SUBTOTAL(function_num,ref1,[ref2],...)
Hover argument names in the table for details.
Output typeNumber
Popularity80 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =SUBTOTAL() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
function_num |
Any type | Required | The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded. 1 = 101; 2 = 102; 3 = 103; 4 = 104; 5 = 105; 6 = 106; 7 = 107; 8 = 108; 9 = 109; 10 = 110; 11 = 111. |
ref1 |
Number | Required | The first named range or reference for which you want the subtotal. |
ref2 |
Number | Optional | ,... Optional. Named ranges or references 2 to 254 for which you want the subtotal. |
▣ Examples
▦ Data
| Cell | Value |
|---|---|
A2 |
120 |
A3 |
10 |
A4 |
150 |
A5 |
23 |
A6 |
120 |
A7 |
10 |
A8 |
150 |
A9 |
23 |
The sum of the subtotal of the cells A2:A5, using 9 as the first argument.
=SUBTOTAL(9,A2:A5)
Result:
303
The average of the subtotal of the cells A2:A5, using 1 as the first argument.
=SUBTOTAL(1,A2:A5)
Result:
75.75
§ Remarks
- If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
- For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
- The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
- The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
- If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.
✦ Tips
- If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
- For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.
- The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
- The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
⚠ Common Errors
#VALUE!
If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.
Fix: Check the first argument
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!