Forum Discussion
How to show autosum when filtering data? (Office 365 for Mac)
Your first concern will be to get your existing spreadsheets up and going but, once that is done, there are many opportunities for refactoring the solutions to make then more transparent or flexible. For example rather than hiding rows using a filter (rather like playing a baby game of peek-a-boo) it is now possible to build a new range containing the filtered records by using the FILTER function to extract the data. CHOOSECOLS can be used to select any particular field you wish and SUM would produce a total.
Since the range will be dynamic, a totals row at the bottom will get in the way so it is quite common to place the totals at the top of the table. Otherwise VSTACK will allow the totals row to be placed at the foot of the table but in such a way that it moves as the table resizes.
Don't let what you already know get in the way of what you could achieve.
I suggest you should adopt the use of Excel Tables for input data. The total row is a selectable part of the Table and will contain a formula such as
=SUBTOTAL(9,[value])
=SUBTOTAL(109,[value])
=SUM([value])To ignore filters use the SUM.
PeterBartholomew1 Thank you, Peter. I converted to a table and it is working great.
I am pleased that things appear to be working out for you. As you said, it is a huge step and I don't think there is yet sufficient experience of the new methods to be sure they are being used to full advantage.
