SUBTOTAL 109 Does Not Exclude Filtered Values

When using the SUBTOTAL function,

I expect the function code 9 to SUM all values in a range unless they are the result of another SUBTOTAL function.
I expect the function code 109 to SUM all VISIBLE values in a range unless they are the result of another SUBTOTAL function.
However, as shown below, 9 and 109 are both including values that are hidden by a filter.
In the picture, the red is indicating the location of hidden rows and the results of the two functions. The two functions are also shown as text one column to the left of their results.

1 Like

Thank you for your feedback. We will take a look and get back on this one.

1 Like

Should this get an internal tracking number like the other bugs Iโ€™ve seen?

1 Like

Hi Adam,

Both function codes i.e. 109 & 9 seem to be correctly excluding filtered-out values. From the referenced snapshot above $19, 589.00 is the total sum of the visible rows only & does not include values from any filtered-out rows.

Believe the source of confusion is that both 9 and 109 are evaluating the same. Function codes 109 and 9 both exclude filtered-out rows however 9 includes hidden rows. In SSDC as of now we do not have hide row functionality, hence 109 and 9 will always evaluate the same in SSDC. Hide-row functionality is in our future roadmap.

Would like to share another example wherein SUBTOTAL functions defined in the header region of sheet calculates aggregate results for table-region data. As a good practice we recommend using aggregate functions in header-region as they are not affected by view filters & sort applied on table-region rows.

Also, taking cue from your example would like to share that similar to header region, having a footer region for such aggregate functions is in our product roadmap.


I see now that theyโ€™re both working correctly. Thanks for the follow-up.

1 Like