PIVOTBY: A Dynamic PivotTable Alternative

This video shows how to write a single formula using the PIVOTBY function to create a dynamic Pivot-style report. Although it isn't a full PivotTable, it is another option that provides a useful alternative in some situations.

This video covers:
- PIVOTBY
- Aggregate functions
- Multiple row fields
- Other arguments
- Grand Totals
- Totals at the top
- Filter

Note: the PIVOTBY function is not available in all versions of Excel.


Chapters in This Video:
00:00 - Introduction
00:07 - PIVOTBY
01:18 - Aggregate Functions
02:23 - Other Arguments

Also, check out these videos with time saving Excel Hacks:

[brianb]

I love the PIVOTBY function, thanks for that!
Any thoughts as to why this formula might return a spill of 8 rows instead of just providing one sum?

TblExecutions is one complete table. One of the columns in that table is Qty - whole numbers, no subtotals.
I used a similar approach on another table, only with a filter and it returned the number I was looking for, but the filter could only return one number. I thought I'd be able to show one "SUM" in this instance as well, but I'm getting a random 8 rows that don't match anything in the table, and the sum of those 8 rows is NOT the sum of Qty in the table.

[rusektor]

Is it bug that GROUPBY doesn't show column name if there's single column?