Buzz
Mastering Excel - Understanding SUBTOTAL function, a powerful tool for subgroup calculations within a list.
Syntax:
=SUBTOTAL (function_num, ref1, [ref2], …)
Where:
Function_num: Mandatory. A number from 1 to 11 or 101 to 111 to specify which function to use in calculating subtotals within the list.
Ref1: Mandatory. The range of reference area initially named that you want to calculate subtotals for.
Ref2…: optional. Can be from 2 to 254 ranges that you want to calculate subtotals for.
Note:
- If there are other nested subtotal functions placed at the ref1, ref2,… arguments, these nested functions will be skipped to avoid double counting.
- For function numbers from 1 to 11, the SUBTOTAL function calculates both hidden values within the data range (hidden rows). For numbers from 101 to 111, it only calculates for visible values within the data range.
- The SUBTOTAL function will ignore any rows hidden by the Filter command (Auto Filter).
Purpose: Calculate a list or database.
SUBTOTAL function is highly versatile yet somewhat challenging to use in Excel due to the need to summon commands using function constants. However, its advantage lies in the ease of modifying calculations by simply replacing its function constants. Alongside the Subtotal calculation support tool, users can effortlessly tackle more complex computation requirements compared to using regular arithmetic operations that the Subtotal function facilitates.
SUBTOTAL is also one of the common calculation functions in Excel, widely used in accounting. Moreover, combining with functions like Vlookup and Hlookup enhances the versatility of the Subtotal function.
