Making the most of the SUBTOTAL function

black and white blank challenge connect

  • 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
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP
  • 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
FormulaDescriptionResult
=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 

Find the remainder with MOD

Build random number generators with RAND

Generate random number between numbers you specify with RANDBETWEEN

Convert a value between measurement systems with CONVERT 

ROUND function

Round a number

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: