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)