APIVOT

Inspired by MrExcel today's YT (16Mar2022) : Sorted Summary Report WIth Totals In Excel Without A Pivot Table - 2487
2 functions to reproduce Pivot Tables functionality:
1st, PIVOT, a lambda helper function, does the calculations corresponding to the "inner" array calculations and can be used anywhere as stand-alone function or inside other functions constructions, can work with ANY function, also can reproduce functionality of all ...IFS functions, without the range argument limitations of ...IFS functions.
2nd, APIVOT, Array Pivot, completes PT layout "cosmetics", does rows/columns labels and grand totals calculations.

PIVOT(r,c,v,fn) Lambda Helper Pivot Table function
r: rows column of an array or table.
c: columns column of an array or table
v: values column of an array or table
fn: lambda helper function argument: LAMBDA(x,function(x))
Note: If fn returns more than a single value, those values will be text joined.
APIVOT(r,c,v,fn,[p],[tc],[tr]) completes the pivot table layout "cosmetics" with labels and grand totals calculations. Calls PIVOT
r,c,v,fn: same argument as previous function
[p]: pivot table name label, if omitted "PT"
[tc]: trailing column label, if omitted "GT"
[tr]: trailing row label, if omitted "GT"

Versatility of PIVOT/APIVOT.
Omitting r or c or both arguments triggers PIVOT function to calculate Grand Totals row/column and Grand Total general.
That functionality is used by APIVOT to attach PT Grand Total elements
......y, VSTACK(uc, PIVOT(r, c, v, fn), PIVOT(, c, v, fn)), z, VSTACK(IF(tr = "", "GT", tr), PIVOT(r, , v, fn), PIVOT(, , v, fn)),.....

Functionality PIVOT/APIVOT when values clm "v" arguments is omitted.

To visualize the simple concept of PIVOT that is capable of creating an array of arrays we can simply use as function argument "fn" LAMBDA(x,x)
And all this is possible using a single MAKEARRAY function construction found in PIVOT. Everything else build around MAKEARRAY in PIVOT is only cosmetics. APIVOT, that calls PIVOT, is only about cosmetics.
In other words, for every intersection row/clm values ("r"/"c") the corresponded matched values array of "v" is extracted. And to these values we can apply any function that can return a single result or more.
Using LAMBDA(x,x) as function argument ("fn"), PIVOT, (respectivly APIVOT) will return all matched results of the correspondent "r"/"c" intersection conditions.
Note: The default delimiter used in PIVOT as separator between values is ",".
If we want to change it, is the first defined variable "d" after LET in PIVOT.
=LAMBDA(r,c,v,fn,LET(d,",",ur,SORT(UNIQUE(r)), …......

Quirks an Features of PIVOT/APIVOT:
- PIVOT, being designed only for calculations, can be used as stand-alone function, or can be called by other formulas or functions constructions.
- APIVOT can be used as presentation/visualizing interface of PIVOT.
- Can handle arrays with no headers.
- Being lambda helper functions, can handle any functions or formulas constructions.
- Can return more than one result.
- All the arguments r,c,v can be/handle array calculations.
- Last but not least, no refresh.
Simple examples to reflect all these. To be continued, asap(time), with a tone of real-life complex task solving scenarios. More functions, more techniques still to come.