How to Use SUBTOTAL 109 and Create Excel Subtotals by Group
Question details
The user wants to generate group-level subtotals in Excel but needs the underlying formula to use SUBTOTAL(109) instead of the default SUBTOTAL(9) to exclude manually hidden rows.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Analyzing a dataset where data needs to be summarized by categories, while ensuring that rows manually hidden by the user do not affect the total sums.
- Observed behavior
- The built-in Subtotal dialog automatically calculates using function number 9 (which includes hidden rows) and does not provide an interface option to change it to 109.
Before applying the Subtotal feature, you must first sort your dataset by the column you wish to group by; otherwise, the subtotals will not cluster correctly when the values change.
Create Grouped Subtotals and Replace with Function 109
Use the Data Subtotal feature to first generate the summaries by group, and then utilize the Find and Replace tool to safely update the formula across the sheet.
Because the standard Subtotal dialog does not offer a direct setting to exclude manually hidden rows (Function 109), the most efficient workaround is to let the system generate the standard formulas first, then batch-update the text of those formulas.
Select a cell in the column you want to group your data by. Go to the 'Data' tab and click 'Sort A to Z' or 'Sort Z to A' to organize the categories.
Highlight your data range, navigate to the 'Data' tab, and click 'Subtotal'. In the dialog box, select your grouped column in 'At each change in', choose 'Sum' under 'Use function', and check the boxes for the columns you want to calculate.
Press 'Ctrl + H' on your keyboard, or go to the 'Home' tab and select 'Find and Replace' to open the dialog.
In the 'Find what' field, type exactly 'SUBTOTAL(9,' (without quotes). In the 'Replace with' field, type 'SUBTOTAL(109,'. Click 'Replace All' to update all formulas.
Effortlessly Calculate Grouped Subtotals with WPS Spreadsheet
WPS Spreadsheet features robust built-in grouping tools and seamless Find & Replace capabilities, making it incredibly easy to summarize datasets and modify formulas in bulk.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook. Sort your target column via the 'Data' tab.
- 2. Insert Subtotals: Under the 'Data' tab, click 'Subtotal'. Configure your grouping and summary options, then click OK.
- 3. Launch Find & Replace: Press 'Ctrl + H' to open the tool, enter 'SUBTOTAL(9,' in the Find box, and 'SUBTOTAL(109,' in the Replace box.
- 4. Execute Replacement: Click 'Replace All' to instantly update all your grouped totals to exclude manually hidden rows.
Frequently Asked Questions
What is the exact difference between SUBTOTAL 9 and 109?
The function SUBTOTAL(9, range) calculates the sum of the range but includes any rows you have manually hidden. SUBTOTAL(109, range) calculates the sum while strictly ignoring any rows that are manually hidden, providing an accurate total only for visible cells.
Why isn't 109 available directly in the Subtotal dialog?
Spreadsheet software defaults to function number 9 (standard Sum) in the Subtotal dialog to prioritize backward compatibility and standard reporting. It does not provide a built-in dropdown to switch this to 109, making the Find and Replace method necessary.
How do I remove all the subtotals from my worksheet?
To remove the grouped subtotals, click any cell within your subtotaled data. Go to the 'Data' tab, click on 'Subtotal', and then click the 'Remove All' button located at the bottom of the dialog box.
