- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.
This post will teach you how to use the SUBTOTAL function effectively in Microsoft Excel. It will cover a summary of the SUBTOTAL function, its formula syntax, essential conditions of its use, and also an example for you to see how it works in practice.
Summary of the SUBTOTAL Function
The SUBTOTAL function is a mathematical function which obtains a subtotal in a list or database. To use the SUBTOTAL function effectively, it is usually simpler to make a list with subtotals by employing the Subtotal command. This is located in the Outline group on the Data tab in the Excel desktop application. After the subtotal list is produced, you can amend it by modifying the SUBTOTAL function.
Syntax of the SUBTOTAL Function
SUBTOTAL(function_num,ref1,[ref2],…)
The SUBTOTAL function syntax contains these particular arguments:
- Function_num – Required. The number 1-11 or 101-111 that confirms the function to relay for the subtotal. 1-11 contains manually-hidden rows, while 101-111 discounts them; filtered-out cells are constantly omitted.
| Function_num (includes hidden values) | Function_num (ignores hidden values) | Function |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
- Ref1 – Required. The first named range or reference in which you are seeking the subtotal.
- Ref2,… – Optional. Named ranges or references 2 to 254 that you are scanning for obtaining the subtotal.
Remarks
The following points are important to remember to ensure that you are always use the SUBTOTAL function effectively for your particular purposes in Excel.
- If there are other subtotals inside ref1, ref2,… (or nested subtotals), these nested subtotals are skipped to avoid the possibility of double counting.
- For the function_num constants from 1 to 11, the SUBTOTAL function has the values of rows hidden by the Hide Rows command. This is found below the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Apply these constants anytime you aim to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function skips values of rows hidden by the Hide Rows command. Employ these constants once you are ready to subtotal only nonhidden numbers in a list.
- The SUBTOTAL function dismisses any rows that are missing in the result of a filter, regardless of which function_num value you use.
- The SUBTOTAL function is customised for columns of data, or vertical ranges. It is incompatible with rows of data, or horizontal ranges. For example, if you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column has no impact on the subtotal. However, hiding a row in a subtotal of a vertical range influences the subtotal.
- In the event that any of the references are 3-D references, SUBTOTAL yields the #VALUE! error value.
Example
Copy the sample data below from the following table, and paste it into cell A1 of a new Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you must at any time, you can extend the column widths to gain a fuller picture of the entire dataset.
| Data | ||
|---|---|---|
| 120 | ||
| 10 | ||
| 150 | ||
| 23 | ||
| Formula | Description | Result |
| =SUBTOTAL(9,A2:A5) | The sum of the subtotal of the cells A2:A5, using 9 as the first argument. | 303 |
| =SUBTOTAL(1,A2:A5) | The average of the subtotal of the cells A2:A5, using 1 as the first argument. | 75.75 |
| Notes | ||
| The SUBTOTAL function invariably needs a numeric argument (1 through 11, 101 through 111) as its first argument. This numeric argument is imported to the subtotal of the values (cell ranges, named ranges) that are defined as the arguments that come after. |
Other Maths Functions Topics
Round a number to the decimal places I want
Build random number generators with RAND
Generate random number between numbers you specify with RANDBETWEEN
Convert a value between measurement systems with CONVERT
Other Links
Back to Software Bytes – Official Website