Excel's regular SUM function includes all values in a range even if they are hidden by a filter or the right click Hide feature. This can be quite tedious when trying to create interactive spreadsheets that can utilize such controls. Using he SUBTOTAL function is the easiest way to overcome this limitation and sum filtered values.
Syntax
=SUBTOTAL(9, range to sum) (to not ignore hidden cells)
=SUBTOTAL(109, RANGE TO SUM) (to not ignore hidden cells)
Steps
- Begin by typing in =SUBTOTAL(
- Continue with 9, or 109,
- Select or typing the range reference that contains the range to be added (i.e. H3:H10)
- Finish the formula by typing in ) and press Enter
How
The SUBTOTAL function is a function that can do a lot of things as itt can essentially do what 11 separate functions could. The feature we're interested in this article is its ability to ignore filtered and manually hidden cells.
The SUBTOTAL function takes 2 arguments. The first one determines the functionality (i.e. one of the 11) and the second is the range to be evaluated. To use the sum action, type in 9 or 109 - both define the sum action. While the 9 ignores manually hidden cells 109 doesn't.
Next, select the sum range as you would in a regular SUM function.
=SUBTOTAL(9,H3:H10)
=SUBTOTAL(109,H3:H10)
