Sum only visible cells or rows in a filtered list
We usually apply the SUM function to sum a list of values directly. However, if you want to sum only visible cells in a filtered list in Excel, using the SUM function will include the hidden rows into the calculation. This tutorial demonstrates a formula based on the SUBTOTAL function with a specified function number to help you get it done.
How to sum only visible cells or rows in a filtered list?
As shown in the screenshot below, there is a fruits sales table, and you want to sum only the visible cells in column C after filtering. You can apply the following formula to get the result.
=SUBTOTAL (function_num, ref1, [ref2],...)
Function_num (required): It is a number from 1 to 11 or from 101 to 111 that specifies which function to use for the subtotal;
Tips: the numbers 9 and 109 can be used to define the SUM function.
Ref1 (required): It is the first named range or reference for which you want to sum only the visible cells;
Ref2,... (optional): It is the named ranges or references (up to 254) for which you want to sum only the visible cells.
How to use this formula?
Select a cell, enter the formula below and press the Enter key to get the result.
Or you can also apply the following formula:
Difference between the function numbers 9 and 109
9 includes manually-hidden rows into calculation, while 109 excludes them. For example, you have a list of data with manually-hidden rows (right-click > hide), to sum only the visible rows, you need to apply the SUBTOTAL function with the function number “109”.
Excel SUBTOTAL function
The Excel SUBTOTAL function returns a subtotal in a list or database.
The Best Office Productivity Tools
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Please leave your comments in English