What is subtotal function in Excel with example?
Use the SUBTOTAL function to summarize amounts, and exclude filtered or hidden rows. For Excel 2010 and later AGGREGATE function is also available, and has more options and functions.
…
11 Functions for Subtotal.
…
11 Functions for Subtotal.
| Function | Numbers |
|---|---|
| COUNT | 2 or 102 |
| COUNTA | 3 or 103 |
| MAX | 4 or 104 |
| MIN | 5 or 105 |
•
Jul 10, 2021
What does the subtotal function in Excel do?
What is the SUBTOTAL Function in Excel? The SUBTOTAL function in Excel allows users to create groups and then perform various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT, MAX, etc. Thus, the SUBTOTAL function in Excel helps in analyzing the data provided.
What does Subtotal 109 mean in Excel?
The SUBTOTAL function ignores rows hidden by a filter and calculates the correct result. Note: 109 is the argument for Sum if you use the SUBTOTAL function.
Why use Subtotal instead of sum in Excel?
3 Answers. The big difference between SUBTOTAL and SUM is that SUBTOTAL can be used repeatedly in the same column for section subtotals and then used again at the end for a grand total. SUBTOTAL(9, myrange) excludes other SUBTOTAL-calculated values within myrange.
How do you use Subtotal 9 in Excel?
Subtotal Function in Excel is a wonderful formula that can be used to perform some specific arithmetic and logical operation on a defined range of cells.
…
Syntax of Subtotal Function:
…
Syntax of Subtotal Function:
| Operation Code | Operation Name | Description |
|---|---|---|
| 9 | SUM | Calculates the Sum of specified range. |
| 10 | VAR | Estimates variance in the specified range. |
What does Subtotal 3 do in Excel?
The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
How do you do Subtotal 109 in Excel?
The Excel SUBTOTAL function with function_num 101-111 neglects values in hidden rows, but not in hidden columns. For example, if you use a formula like SUBTOTAL(109, A1:E1) to sum numbers in a horizontal range, hiding a column won’t affect the subtotal.
Under which tab Subtotal option is available in Excel?
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.
What is the meaning of 9 in subtotal formula?
SUM function
9 represents the SUM function, for all the cells from the indicated range. There is another version of the SUM function in SUBTOTAL: Subtotal(109,E1:E132).
What is the shortcut key for subtotal in Excel?
Select a cell in the column or the blank cell below the filtered range. Press the AutoSum button or keyboard shortcut (Alt+=). The SUBTOTAL formula will be automatically inserted in the cell.
What is the difference between Subtotal 9 and 109 in Excel?
SUBTOTAL(9,…) totals everything except cells with subtotals and filtered cells. SUBTOTAL(109,…) totals everything except cells with subtotals and filtered cells and hidden rows.