Forum Discussion

Charlie77's avatar
Charlie77
Copper Contributor
May 25, 2020

Apply subtotal to formatted structure

Hi, I just realized that function subtotal's "including hidden values" index has no effect to a table (i.e. formatted structure). Say I have a table MyTable with two columns [Type] and [Value], Subtotal(9, MyTable[Value]) and Subtotal(109, MyTable[Value]) give the same result - the total of the visible values once the table is filtered by [Type] with its header filter. I initially thought Subtotal(9, MyTable[Value]) would include hidden rows as well.  Is this meant to be like this? Thanks for any advice. 

Charlie  

excel

2 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    May 25, 2020
    9 - exclude subtotals and rows hidden by filter.
    109 - exclude subtotals, rows hidden by filter, and rows hidden manually.

    With filter turned on, I don't think you will notice a difference between the two. If you turn the filter off completely and then manually hide rows, you should see the difference.
    • Charlie77's avatar
      Charlie77
      Copper Contributor
      May 25, 2020

      JMB17 Yes, I think you're right. I tried a few times with table and non-table data and found virtually the difference of subtotal(10X,??) and subtotal(X,??) is nonresistant for the built-in header filter - both excluding hidden cells. The difference of the two only exists when the rows are manually hidden i.e. select the rows, right clicking and hide. Thanks for the advice.