Forum Discussion
Different functions for each column in a subtotal
ive got a sheet where i want to subtotal using different functions in different columns. i will always have different data every time i want this to execute, so cant hardwire the row range, also each group (based on date) will have different number of rows.
Eg, on change of date, sum column B, count column C, max column D.
I hope i explained the problem adequately.
4 Replies
Another option is simple cached PivotTable
That requirement is served by a new(ish) function introduced within 365. Using OliverScheurich​ data
= GROUPBY(Date, Values, HSTACK(SUM, COUNT, MAX), , 0)gives the desired results, sorted by date.
With modern Excel and with legacy Excel such as Excel 2013 you can use Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.
Thanks. I didn't know about powerquery.
I did achieve a result by attaching the sheet to Access. Though PowerQuery is more elegant.
