Filtered rows in Structured Table (Excel) 1

[Deniall]

Suppose I have a very simple (and entirely hypothetical) structured table in Excel containing data about people.[ ] Its columns might be:[ ] Name, DateOfBirth, Salary, Department.

Outside the table, above it and aligned with the appropriate table columns, I have cells containing correctly structured formulae for:[ ] Earliest date of birth;[ ] Latest date of birth;[ ] and[ ] Average salary.[ ] At this stage I have no filters applied, so all the table's rows are displayed and the formulae I describe above are doing exactly what I would expect.[ ] So far, so good.[ ]

Now I apply some table filtering.[ ] I click on the down-arrow inside the header of the Department column, and restrict the table to displaying only the entries for the "Clothing" department.[ ] The cells for averages etc that are outside the table still reflect the entire table rather than being restricted to only the entries that are currently displayed inside the table.[ ] That's fine, but[ ]…

Is there a way that I can have my "averages etc" cells apply only to the table's displayed entries?

Alternatively, is there a way to create an extra column inside the table that indicates whether the row is currently displayed?[ ] If so, then I could use the entry in this extra column to condition my "averages etc" cells to achieve what I am after.

I have tried using everyone's favourite search engine to get an answer to this, but get completely inundated with irrelevant replies.[ ] I am using Excel[ ]2010 so I do not have access to the FILTER() function.

 

[combo]

The SUBTOTAL function summarizes visible rows. Formulas for structured table totals can be copied anywhere (with table name added before field).

combo

 

[Deniall]

Thanks, Combo.[ ] Reads like SUBTOTAL (previously unknown to me) will do the trick.[ ]Cannot test it right now, but should get an opportunity in a few hours.[ ] Will report back then.

 

[Deniall]

Got it working.[ ] Thanks again.

I decided I would get more future flexibility if I took the approach of including in my table (be it structured or non-structured) of including in the table a column that contains a boolean entry of True or False according to whether the row is visible or not visible.[ ] It took a bit of exploration to find a way, but I eventually homed in on

=NOT(ISERR(SUBTOTAL(101,CellAddress)))

where "CellAddress" is the address of some other cell in the same row.[ ] For this to work, "CellAddress" cannot be non-numeric, and cannot be empty.[ ] These restrictions will not usually be very onerous, but if (say) your table has all columns being text then you simply create TWO extra columns rather than one.[ ] First a dummy column guaranteed to always be numeric, such as simply the number 1 (or even 0).[ ] Second a column containing an appropriately edited version of the above formula.

This works regardless of whether the row is unshown because it is Hidden or because it has been "filtered" out.

Any suggestions of a slightly "cleaner" approach will be welcome, but not essential.

 

[Deniall]

I have now implemented into my spreadsheet the ability to limit "outside-the-table" calculations to those table rows that are visible.[ ] In case anyone else has a similar need, I will describe the method I ended up using.

As per my earlier post (27Jan23@05:50), I decided my table needed to include an extra column whose value would be True or False according to whether the row was visible or hidden.[ ] I named this column "Visible", and the formula in it was
[ ][ ][ ][ ]=NOT(ISERR(SUBTOTAL(101,[@ExtraColumn2])))
which is unchanged from my previous post except that ExtraColumn2 has to be an additional extra column that cannot be used for any other purpose.

What caused me some extra grief on the way through was what to use for ExtraColumn2.[ ] My first few attempts were not robust enough.[ ] I ended up using an approach that required another dedicated column, this one with a one-character title.[ ] The formula for the column was
[ ][ ][ ][ ]=LEN(Table1[[#Headers],[x]])

So far all the operations I have tested can be restricted to rows with Visible=TRUE.