How to Sum Only Visible Rows in Excel Using SUBTOTAL
Question details
The user needs to calculate the sum of only the visible rows in a dataset that has been filtered or contains hidden rows.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating totals on a dataset where specific rows are filtered out or manually hidden from view.
- Observed behavior
- Using the standard SUM function includes all rows (both visible and hidden) in the total, resulting in an incorrect calculation for the currently filtered view.
Ensure that the data range you intend to sum contains true numeric values, as numbers accidentally stored as text will not be calculated. Also, verify whether your rows are hidden manually or hidden by a data filter, as this determines the exact function number you will need to use.
Use SUBTOTAL(9, range) for Filtered Rows
The standard method to sum visible cells while ignoring rows that have been hidden using data filters.
The SUBTOTAL function is designed specifically for calculating data in filtered lists. By using the function number 9, you instruct the program to perform a SUM operation while completely ignoring any rows that the filter has removed from view.
Click on the blank cell below your dataset where you want the sum result to appear.
Type the formula =SUBTOTAL(9, range) into the formula bar. Replace 'range' with your actual data cells (for example, =SUBTOTAL(9, Q5:Q154)).
Press Enter to apply the formula. The cell will now display the sum of only the visible, unfiltered rows.
Use SUBTOTAL(109, range) for Manually Hidden Rows
Use this formula variation if you have manually hidden rows (via right-click > Hide) rather than using a standard data filter.
Sum Visible Rows Effectively with WPS Spreadsheet
WPS Spreadsheet offers full compatibility with standard Excel functions, including SUBTOTAL, allowing you to easily filter large datasets and calculate visible rows accurately.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your data.
- 2. Apply filters: Navigate to the Data tab and click 'Filter' to filter your dataset to the desired view.
- 3. Insert the SUBTOTAL function: In a blank cell below your data, type =SUBTOTAL(9, [select your range]) and press Enter to instantly sum only the visible results.
Frequently Asked Questions
Why is my SUM function including hidden rows?
The standard =SUM() function is designed to add up all values in a specified range, regardless of their visibility. To exclude hidden or filtered rows from your calculation, you must use the =SUBTOTAL() or =AGGREGATE() function instead.
What is the difference between SUBTOTAL 9 and 109?
Function number 9 performs a sum that ignores rows hidden by a filter, but it will still include rows that were manually hidden. Function number 109 ignores both filtered rows and manually hidden rows, summing only the truly visible cells.
Can I use SUBTOTAL to average visible rows instead of summing them?
Yes. By changing the first argument (the function number), you can perform different calculations on visible rows. For example, use =SUBTOTAL(1, range) to calculate the average, or =SUBTOTAL(3, range) to count the visible rows.
Why does my SUBTOTAL formula return 0?
This usually happens if the data in your range is formatted as text rather than numbers. Select your data, right-click, choose 'Format Cells,' and ensure the category is set to Number. You may need to double-click the cells and press Enter to convert them, or use the 'Convert to Number' error-checking option.
