A few months late for original poster, but as of September 23 2024 there are two new functions available in Excel for Microsoft 365: GROUPBY and PIVOTBY

These will do exactly what was requested. I have just used for the first time, and while at first glance they seem like "pivot table in a formula", I was able to do something not possible with a pivot table - dynamically filter by date greater than "TODAY() - X", which was very handy.

You can also add options to make minimum headers, totals, etc. The results are also directly accessible with normal formulas, not defaulting to and sometimes needing "GETPIVOTDATA". Overall, very cool.