APIVOT

[Xlambda]

APIVOT array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table. calls AAGGREGATE , APPEND2V , APPEND2H
Basic functionality means: as in PT interface we can select rows values, column values, values, value fields, only sum, count, min and max
- disadvantages: was not designed to compete with mighty PT, does not have grouping functionality as PT does, though, could be added but the formulas can get discouraging huge.
- advantages: no table format needed, no refresh needed, so if we filter a large table for some complex criteria in a simple dynamic array, this lambda can be handy.
Other functions on minisheet ASELECT
Note: Sample data, courtesy of Mike Girvin ExcelIsFun

=LAMBDA(a,rw,cl,vl,vf,
    LET(g,"Grand Total",fn,IF(vf=0,0,vf),q,SEQUENCE(,COLUMNS(a)),c,INDEX(a,,cl),r,INDEX(a,,rw),v,INDEX(a,,vl),uc,SORT(UNIQUE(c)),ur,SORT(UNIQUE(r)),tc,TRANSPOSE(uc),f,IF(AND(ISNUMBER(v)),fn,1),
       ar,SWITCH(f,0,SUMIFS(v,r,ur,c,tc),2,MINIFS(v,r,ur,c,tc),3,MAXIFS(v,r,ur,c,tc),1,COUNTIFS(r,ur,c,tc)),fg,SWITCH(f,2,0,3,1,4,7,5),
       rc,AAGGREGATE(ar,fg,),br,TRANSPOSE(AAGGREGATE(TRANSPOSE(ar),fg,)),
       w,ROWS(ar)+1,u,COLUMNS(ar)+2,sr,SEQUENCE(w),sc,SEQUENCE(,u)-1,lc,IF(sr=w,g,INDEX(ur,sr)),tr,SWITCH(sc,0,"("&rw&"\"&cl&")"&" "&vl&" vf="&fn,u-1,g,INDEX(tc,sc)),
       aa,APPEND2V(APPEND2H(ar,rc,),br,),ab,IF(aa="",SWITCH(f,2,MIN(br,rc),3,MAX(br,rc),SUM(rc)),aa),ac,APPEND2V(tr,APPEND2H(lc,ab,),),
       IFS(ISNA(XMATCH(fn,{0,1,2,3})),"check function nr.",NOT(AND(ISNUMBER(XMATCH(CHOOSE({1,2,3},rw,cl,vl),q)))),"check selection",TRUE,ac)
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	sample data, courtesy of Mike Girvin			ExcelIsFun				=APIVOT(Rev,2,4,6,)		vf=0	sum					Sum of Net Revenue	Column Labels
2	Date	Sales Rep	Units	Product	Region	Net Revenue		(2\4) 6 vf=0	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total		Row Labels	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
3	01-01-20	Tynia Malone	500	Quad	MidWest	12560.88		Chantel Mims	7659.57	8379.33	28169.71	29746.59	13486.96	87442.16		Chantel Mims	7659.57	8379.33	28169.71	29746.6	13487	87442.16
4	01-01-20	Kiki Sho	38	Yanaki	MidWest	902.79		Hien Pham	7785.00	7809.94	22079.83	9952.88	19538.68	67166.33		Hien Pham	7785	7809.94	22079.83	9952.88	19538.7	67166.33
5	01-01-20	Hien Pham	500	Yanaki	MidWest	8035.63		Janis Figueroa	161.70	1104.42	18189.42	5683.15	3039.75	28178.44		Janis Figueroa	161.7	1104.42	18189.42	5683.15	3039.75	28178.44
6	01-01-20	Chantel Mims	500	Yanaki	West	8035.63		Kiki Sho	9137.04	6161.93	2809.60	5981.80	10598.57	34688.94		Kiki Sho	9137.04	6161.93	2809.6	5981.8	10598.6	34688.94
7	01-01-20	Tynia Malone	39	Quad	East	1481.47		Tynia Malone	948.47	339.50	38904.91	2434.63	14517.63	57145.14		Tynia Malone	948.47	339.5	38904.91	2434.63	14517.6	57145.14
8	01-01-20	Kiki Sho	48	Sunset	MidWest	934.2		Grand Total	25691.78	23795.12	110153.5	53799.05	61181.59	274621.01		Grand Total	25691.78	23795.12	110153.47	53799.1	61181.6	274621.01
9	01-01-20	Hien Pham	13	Quad	Canada	580.97
10	02-01-20	Tynia Malone	27	Yanaki	NorthWest	641.45		=APIVOT(Rev,2,4,6,1)		vf=1	count
11	02-01-20	Hien Pham	13	Sunset	MidWest	337.35		(2\4) 6 vf=1	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
12	02-01-20	Chantel Mims	36	Sunset	MidWest	732.87		Chantel Mims	7	5	6	7	6	31
13	02-01-20	Tynia Malone	44	Quad	South	1596.61		Hien Pham	2	4	5	6	7	24
14	03-01-20	Hien Pham	100	Crested Beaut	MidWest	1816.5		Janis Figueroa	1	2	4	2	1	10
15	03-01-20	Chantel Mims	7	Yanaki	Mexico	188.65		Kiki Sho	5	2	3	4	5	19
16	03-01-20	Hien Pham	5	Yanaki	Mexico	139.75		Tynia Malone	1	1	7	7	3	19
17	03-01-20	Chantel Mims	37	Quad	Mexico	1374.05		Grand Total	16	14	25	26	22	103
18	03-01-20	Janis Figueroa	525	Quad	Mexico	13188.92
19	03-01-20	Chantel Mims	450	Sunset	NorthWest	6714.56		=APIVOT(Rev,2,4,6,2)		vf=2	min					Min of Net Revenue	Column Labels
20	04-01-20	Hien Pham	45	Sunset	Canada	916.09		(2\4) 6 vf=2	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total		Row Labels	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
21	04-01-20	Tynia Malone	3	Sunset	West	74.85		Chantel Mims	215.6	209.7	312.83	639.67	26.95	26.95		Chantel Mims	215.6	209.7	312.83	639.67	26.95	26.95
22	04-01-20	Chantel Mims	48	Crested Beaut	West	934.2		Hien Pham	1816.5	33.95	218.45	24.95	55.9	24.95		Hien Pham	1816.5	33.95	218.45	24.95	55.9	24.95
23	04-01-20	Chantel Mims	100	Crested Beaut	MidWest	1746.5		Janis Figueroa	161.7	34.95	85.38	174.65	3039.75	34.95		Janis Figueroa	161.7	34.95	85.38	174.65	3039.75	34.95
24	04-01-20	Hien Pham	2	Yanaki	South	55.9		Kiki Sho	249.5	305.55	305.83	311.4	57.9	57.9		Kiki Sho	249.5	305.55	305.83	311.4	57.9	57.9
25	04-01-20	Kiki Sho	25	Quad	Mexico	907.16		Tynia Malone	948.47	339.5	298.83	74.85	641.45	74.85		Tynia Malone	948.47	339.5	298.83	74.85	641.45	74.85
26	04-01-20	Chantel Mims	8	Crested Beaut	South	215.6		Grand Total	161.7	33.95	85.38	24.95	26.95	24.95		Grand Total	161.7	33.95	85.38	24.95	26.95	24.95
27	04-01-20	Kiki Sho	34	Crested Beaut	MidWest	778.86
28	04-01-20	Chantel Mims	275	Yanaki	West	4419.59		=APIVOT(Rev,2,4,6,3)		vf=3	max
29	05-01-20	Chantel Mims	200	Quad	East	5976.6		(2\4) 6 vf=3	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
30	05-01-20	Hien Pham	600	Yanaki	East	9297.75		Chantel Mims	2724.75	6291.25	14114.38	7531.78	8035.63	14114.38
31	05-01-20	Janis Figueroa	36	Majestic Beaut	MidWest	1069.47		Hien Pham	5968.5	6291.25	13816.96	7574.19	9297.75	13816.96
32	05-01-20	Chantel Mims	28	Quad	MidWest	1039.82		Janis Figueroa	161.7	1069.47	13188.92	5508.5	3039.75	13188.92
33	05-01-20	Kiki Sho	525	Yanaki	MidWest	8437.41		Kiki Sho	5021.19	5856.38	1596.61	4191.25	8437.41	8437.41
34	05-01-20	Chantel Mims	6	Majestic Beaut	Canada	209.7		Tynia Malone	948.47	339.5	12560.88	934.2	7633.84	12560.88
35	05-01-20	Kiki Sho	9	Majestic Beaut	West	305.55		Grand Total	5968.5	6291.25	14114.38	7574.19	9297.75	14114.38
36	05-01-20	Hien Pham	550	Quad	MidWest	13816.96
37	06-01-20	Kiki Sho	250	Sunset	East	4191.25		=APIVOT(Rev,2,4,3,)								Sum of Units2	Column Labels
38	06-01-20	Tynia Malone	7	Quad	Canada	298.83		(2\4) 3 vf=0	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total		Row Labels	Crested Beaut	Majestic Beaut	Quad	Sunset	Yanaki	Grand Total
39	06-01-20	Chantel Mims	150	Crested Beaut	Canada	2724.75		Chantel Mims	401	321	1022	2005	817	4566		Chantel Mims	401	321	1022	2005	817	4566
40	06-01-20	Tynia Malone	43	Crested Beaut	MidWest	948.47		Hien Pham	500	298	856	660	1189	3503		Hien Pham	500	298	856	660	1189	3503
41	07-01-20	Kiki Sho	21	Sunset	NorthWest	544.95		Janis Figueroa	6	37	677	407	150	1277		Janis Figueroa	6	37	677	407	150	1277
42	07-01-20	Hien Pham	13	Quad	West	554.97		Kiki Sho	560	309	76	331	614	1890		Kiki Sho	560	309	76	331	614	1890
43	07-01-20	Tynia Malone	24	Sunset	West	529.38		Tynia Malone	43	10	1471	111	877	2512		Tynia Malone	43	10	1471	111	877	2512
44	07-01-20	Tynia Malone	15	Sunset	Mexico	389.25		Grand Total	1510	975	4102	3514	3647	13748		Grand Total	1510	975	4102	3514	3647	13748
45	08-01-20	Kiki Sho	2	Yanaki	MidWest	57.9
46	08-01-20	Tynia Malone	4	Sunset	NorthWest	99.8		=APIVOT(Rev,4,5,3,)										Sum of Units	Column Labels
47	08-01-20	Kiki Sho	42	Yanaki	East	997.82		(4\5) 3 vf=0	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total		Row Labels	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total
48	08-01-20	Chantel Mims	500	Sunset	East	7460.63		Crested Beaut	150	169	411	283	0	408	89	1510		Crested Beaut	150	169	411	283		408	89	1510
49	08-01-20	Hien Pham	38	Majestic Beaut	East	1161.19		Majestic Beaut	15	38	10	336	57	259	260	975		Majestic Beaut	15	38	10	336	57	259	260	975
50	08-01-20	Tynia Malone	48	Sunset	Mexico	934.2		Quad	170	241	638	1078	275	1255	445	4102		Quad	170	241	638	1078	275	1255	445	4102
51	08-01-20	Chantel Mims	29	Sunset	South	639.67		Sunset	55	802	664	497	482	29	985	3514		Sunset	55	802	664	497	482	29	985	3514
52	09-01-20	Hien Pham	45	Yanaki	Canada	1069.09		Yanaki	45	824	21	1065	539	377	776	3647		Yanaki	45	824	21	1065	539	377	776	3647
53	09-01-20	Tynia Malone	10	Sunset	Canada	239.5		Grand Total	435	2074	1744	3259	1353	2328	2555	13748		Grand Total	435	2074	1744	3259	1353	2328	2555	13748
54	09-01-20	Kiki Sho	7	Quad	West	305.83
55	09-01-20	Janis Figueroa	6	Crested Beaut	MidWest	161.7		=APIVOT(Rev,4,5,6,)										Sum of Net Revenue	Column Labels
56	09-01-20	Chantel Mims	1	Yanaki	West	26.95		(4\5) 6 vf=0	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total		Row Labels	Canada	East	Mexico	MidWest	NorthWest	South	West	Grand Total
57	09-01-20	Tynia Malone	475	Yanaki	NorthWest	7633.84		Crested Beaut	2724.75	3153.69	6338.65	5452.03	0	6184.1	1838.56	25691.78		Crested Beaut	2724.75	3153.69	6338.65	5452.03		6184.1	1838.56	25691.78
58	09-01-20	Chantel Mims	33	Crested Beaut	Mexico	699.85		Majestic Beaut	524.25	1161.19	339.5	6925.85	1598.78	6614.8	6630.75	23795.12		Majestic Beaut	524.25	1161.19	339.5	6925.85	1598.78	6614.8	6630.75	23795.12
59	09-01-20	Janis Figueroa	75	Quad	Canada	2457.56		Quad	5794.92	7543.45	17379.57	27417.66	6908.48	33327.47	11781.9	110153.47		Quad	5794.92	7543.45	17379.6	27417.66	6908.48	33327.5	11781.9	110153.47
60	09-01-20	Chantel Mims	50	Majestic Beaut	NorthWest	1348.13		Sunset	1155.59	12777.58	9997.94	7512.92	7533.96	639.67	14181.4	53799.05		Sunset	1155.59	12777.6	9997.94	7512.92	7533.96	639.67	14181.4	53799.05
61	10-01-20	Janis Figueroa	7	Sunset	NorthWest	174.65		Yanaki	1069.09	14095.56	579.95	17433.73	9222.85	6298.24	12482.2	61181.59		Yanaki	1069.09	14095.6	579.95	17433.73	9222.85	6298.24	12482.2	61181.59
62	10-01-20	Chantel Mims	575	Quad	South	14114.38		Grand Total	11268.6	38731.47	34635.61	64742.19	25264.07	53064.28	46914.8	274621.01		Grand Total	11268.6	38731.5	34635.6	64742.19	25264.07	53064.3	46914.8	274621.01
63	10-01-20	Janis Figueroa	150	Yanaki	East	3039.75
64	11-01-20	Kiki Sho	300	Majestic Beaut	MidWest	5856.38
65	11-01-20	Janis Figueroa	400	Sunset	MidWest	5508.5		dynamic APIVOT interface using		ASELECT	(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)
66	11-01-20	Chantel Mims	32	Yanaki	East	760.24		date 1	date 2	row start	row end
67	11-01-20	Chantel Mims	28	Crested Beaut	Mexico	617.61		03-01-20	05-01-20	12	27
68	11-01-20	Kiki Sho	41	Crested Beaut	West	904.36						=APIVOT(H70#,1,2,3,)
69	11-01-20	Chantel Mims	425	Sunset	West	5852.78		=ASELECT(Rev,J67,K67,,,{2,4,6})				(1\2) 3 vf=0	Crested Beaut	Quad	Sunset	Yanaki	Grand Total
70	12-01-20	Chantel Mims	6	Majestic Beaut	NorthWest	215.7		Hien Pham	Crested Beaut	1816.5		Chantel Mims	2896.3	7350.65	6714.56	4608.24	21569.75
71	12-01-20	Hien Pham	1	Majestic Beaut	West	33.95		Chantel Mims	Yanaki	188.65		Hien Pham	1816.5	0	916.09	195.65	2928.24
72	12-01-20	Chantel Mims	40	Sunset	East	814.3		Hien Pham	Yanaki	139.75		Janis Figueroa	0	13188.92	0	0	13188.92
73	12-01-20	Hien Pham	28	Yanaki	NorthWest	689.01		Chantel Mims	Quad	1374.05		Kiki Sho	778.86	907.16	0	0	1686.02
74	12-01-20	Chantel Mims	34	Crested Beaut	East	721.06		Janis Figueroa	Quad	13188.92		Tynia Malone	0	0	74.85	0	74.85
75	12-01-20	Hien Pham	550	Sunset	Mexico	7574.19		Chantel Mims	Sunset	6714.56		Grand Total	5491.66	21446.73	7705.5	4803.89	39447.78
76	13-01-20	Chantel Mims	7	Quad	Mexico	312.83		Hien Pham	Sunset	916.09
77	13-01-20	Hien Pham	9	Yanaki	Mexico	251.55		Tynia Malone	Sunset	74.85
78	13-01-20	Kiki Sho	10	Crested Beaut	East	249.5		Chantel Mims	Crested Beaut	934.2		top left corner cell format
79	13-01-20	Chantel Mims	250	Majestic Beaut	South	6291.25		Chantel Mims	Crested Beaut	1746.5		(rows\columns) values value field=
80	13-01-20	Hien Pham	9	Majestic Beaut	South	323.55		Hien Pham	Yanaki	55.9		kept it in this shape because if we pivot regular arrays, they do not have headers
81	13-01-20	Tynia Malone	375	Yanaki	South	6242.34		Kiki Sho	Quad	907.16
82	14-01-20	Tynia Malone	425	Quad	South	10921.12		Chantel Mims	Crested Beaut	215.6
83	14-01-20	Hien Pham	275	Quad	NorthWest	6908.48		Kiki Sho	Crested Beaut	778.86
84	14-01-20	Kiki Sho	44	Quad	Mexico	1596.61		Chantel Mims	Yanaki	4419.59
85	14-01-20	Janis Figueroa	1	Majestic Beaut	NorthWest	34.95		Chantel Mims	Quad	5976.6
86	14-01-20	Kiki Sho	125	Crested Beaut	East	2183.13
APIVOT post

Cell Formulas
Range	Formula
H1,H69,L68,H55,H46,H37,H28,H19,H10	H1	=FORMULATEXT(H2)
H2:N8	H2	=APIVOT(Rev,2,4,6,)
H11:N17	H11	=APIVOT(Rev,2,4,6,1)
H20:N26	H20	=APIVOT(Rev,2,4,6,2)
H29:N35	H29	=APIVOT(Rev,2,4,6,3)
H38:N44	H38	=APIVOT(Rev,2,4,3,)
H47:P53	H47	=APIVOT(Rev,4,5,3,)
H56:P62	H56	=APIVOT(Rev,4,5,6,)
J67	J67	=XMATCH(H67,Rev[Date],1)
K67	K67	=XMATCH(I67,Rev[Date],-1)
L69:Q75	L69	=APIVOT(H70#,1,2,3,)
H70:J85	H70	=ASELECT(Rev,J67,K67,,,{2,4,6})
Dynamic array formulas.

 

[Xlambda]

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.

=LAMBDA(r,c,v,fn,
    LET(
        d, ",",ur, SORT(UNIQUE(r)),uc, SORT(UNIQUE(c)),
        w, ROWS(ur),l, ROWS(uc),
        MAKEARRAY(w,l,LAMBDA(y,x,
                LET(
                    a, INDEX(ur, y),b, INDEX(uc, x),
                    i, IF((a = r) * (b = c), v, ""),f, FILTER(i, i <> ""),
                    fx, IF(ISERR(SUM(f)), "", IFERROR(fn(f), "")),
                    IF(COUNTA(fx) > 1, TEXTJOIN(d, , fx), fx))))
    )
)

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"

=LAMBDA(r, c, v, fn, [p], [tr], [tc],
    LET(
        ur, SORT(UNIQUE(r)),
        uc, TOROW(SORT(UNIQUE(c))),
        x, VSTACK(IF(p = "", "PT", p), ur, IF(tc = "", "GT", tc)),
        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)),
        a, HSTACK(x, y, z),
        FILTER(FILTER(a, TAKE(a, 1) <> 0), TAKE(a, , 1) <> 0)
    )
)

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		sample array (not table)					r,"Product" clm,c,omitted,v,"Sales" clm, fn, sum/count
2		Team	Name	Product	Sales		c,omitted => only grand totals will be displayed, similar to sumifs/countifs functionality
3		Red	Amanda	Apple	559		=APIVOT(D3:D28,,E3:E28,LAMBDA(x,SUM(x)),"Product","Sales","Total")
4		Red	Thiago	Kiwi	40		↓↓↓↓								=APIVOT(D3:D28,,E3:E28,LAMBDA(x,COUNT(x)),"Product","Count","Total")
5		Red	Carol	Lemon	808		Product	Sales		=SUMIFS(E3:E28,D3:D28,G6:G11)					Product	Count		=COUNTIFS(D3:D28,O6:O11)
6		Blue	Daniel	Lime	200		Apple	3611		3611					Apple	6		6
7		Red	Daniela	Apple	903		Kiwi	40		40					Kiwi	1		1
8		Blue	Eduardo	Orange	656		Lemon	3108		3108					Lemon	6		6
9		Red	Gabriel	Lemon	404		Lime	2585		2585					Lime	6		6
10		Blue	Helena	Lime	526		Orange	2801		2801					Orange	6		6
11		Blue	Bernando	Orange	291		Quince	578		578					Quince	1		1
12		Red	Isabella	Apple	559		Total	12723							Total	26
13		Blue	Juao Pedro	Orange	485
14		Red	Jose	Lemon	354		r,"Product" clm, c,"Team" clm, v,"Sales" clm, fn,sum, p,Prd/Team, tc,tr,omitted
15		Blue	Stephanie	Lime	270		=APIVOT(D3:D28,B3:B28,E3:E28,LAMBDA(x,SUM(x)),"Prd/Team")
16		Red	Thiago	Apple	693		Prd/Team	Blue	Red	GT
17		Red	Amanda	Orange	235		Apple	897	2714	3611
18		Blue	Bernando	Lemon	610		Kiwi		40	40
19		Red	Carol	Lime	619		Lemon	1542	1566	3108
20		Blue	Daniel	Apple	612		Lime	996	1589	2585
21		Red	Daniela	Orange	596		Orange	1432	1369	2801
22		Blue	Eduardo	Lemon	448		Quince	578		578
23		Red	Gabriel	Lime	851		GT	5445	7278	12723
24		Blue	Helena	Apple	285
25		Red	Isabella	Orange	538		r,"Name" clm, c,"Product" clm, v,"Sales" clm, fn,average, p,tc,tr,omitted
26		Blue	Juao Pedro	Lemon	484		=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,AVERAGE(x)))
27		Red	Jose	Lime	119		PT	Apple	Kiwi	Lemon	Lime	Orange	Quince	GT
28		Blue	Stephanie	Quince	578		Amanda	559				235		397
29							Bernando			610		291		450.5
30							Carol			808	619			713.5
31							Daniel	612			200			406
32							Daniela	903				596		749.5
33							Eduardo			448		656		552
34							Gabriel			404	851			627.5
35							Helena	285			526			405.5
36							Isabella	559				538		548.5
37							Jose			354	119			236.5
38							Juao Pedro			484		485		484.5
39							Stephanie				270		578	424
40							Thiago	693	40					366.5
41							GT	601.8	40	518	430.8	466.8	578	489.3
42
PIV 1

Cell Formulas
Range	Formula
G3	G3	=FORMULATEXT(G5)
O4,G26,G15,R5,J5	O4	=FORMULATEXT(O5)
G5:H12	G5	=APIVOT(D3:D28,,E3:E28,LAMBDA(x,SUM(x)),"Product","Sales","Total")
O5:P12	O5	=APIVOT(D3:D28,,E3:E28,LAMBDA(x,COUNT(x)),"Product","Count","Total")
J6:J11	J6	=SUMIFS(E3:E28,D3:D28,G6:G11)
R6:R11	R6	=COUNTIFS(D3:D28,O6:O11)
G16:J23	G16	=APIVOT(D3:D28,B3:B28,E3:E28,LAMBDA(x,SUM(x)),"Prd/Team")
G27:N41	G27	=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,AVERAGE(x)))
Dynamic array formulas.

 

[Xlambda]

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)),.....

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		sample array (not table)					Functionality PIVOT/APIVOT when r or c or both arguments are omitted
2		Team	Name	Product	Sales		1. c,omitted
3		Red	Amanda	Apple	559		r,"Name" clm, c,omitted, v,"Sales" clm, fn,sum
4		Red	Thiago	Kiwi	40		=PIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))
5		Red	Carol	Lemon	808		↓↓↓↓		=APIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))
6		Blue	Daniel	Lime	200		↓↓↓↓		PT	GT
7		Red	Daniela	Apple	903		794		Amanda	794		2. r,omitted
8		Blue	Eduardo	Orange	656		901		Bernando	901		r,omitted, c,"Product" clm, v,"Sales" clm, fn,sum
9		Red	Gabriel	Lemon	404		1427		Carol	1427			=PIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))
10		Blue	Helena	Lime	526		812		Daniel	812			3611	40	3108	2585	2801	578
11		Blue	Bernando	Orange	291		1499		Daniela	1499
12		Red	Isabella	Apple	559		1104		Eduardo	1104		=APIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))
13		Blue	Juao Pedro	Orange	485		1255		Gabriel	1255		PT	Apple	Kiwi	Lemon	Lime	Orange	Quince	GT
14		Red	Jose	Lemon	354		811		Helena	811		GT	3611	40	3108	2585	2801	578	12723
15		Blue	Stephanie	Lime	270		1097		Isabella	1097
16		Red	Thiago	Apple	693		473		Jose	473		3. r,c, omitted
17		Red	Amanda	Orange	235		969		Juao Pedro	969			=PIVOT(,,E3:E28,LAMBDA(x,SUM(x)))
18		Blue	Bernando	Lemon	610		848		Stephanie	848			12723
19		Red	Carol	Lime	619		733		Thiago	733
20		Blue	Daniel	Apple	612				GT	12723		=APIVOT(,,E3:E28,LAMBDA(x,SUM(x)))
21		Red	Daniela	Orange	596							PT	GT
22		Blue	Eduardo	Lemon	448							GT	12723
23		Red	Gabriel	Lime	851
24		Blue	Helena	Apple	285		Actually this versatility is used on purpose by the design of APIVOT to calculate Grand Totals row/column/general
25		Red	Isabella	Orange	538		and attach them to the final PT layout.
26		Blue	Juao Pedro	Lemon	484
27		Red	Jose	Lime	119		r,"Name" clm, c,"Product" clm, v,"Sales" clm, fn,sum, p,tc,tr,omitted
28		Blue	Stephanie	Quince	578		=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,SUM(x)))
29							PT	Apple	Kiwi	Lemon	Lime	Orange	Quince	GT
30							Amanda	559				235		794
31							Bernando			610		291		901
32							Carol			808	619			1427
33							Daniel	612			200			812
34							Daniela	903				596		1499
35							Eduardo			448		656		1104
36							Gabriel			404	851			1255
37							Helena	285			526			811
38							Isabella	559				538		1097
39							Jose			354	119			473
40							Juao Pedro			484		485		969
41							Stephanie				270		578	848
42							Thiago	693	40					733
43							GT	3611	40	3108	2585	2801	578	12723
44
PIV 2

Cell Formulas
Range	Formula
G4	G4	=FORMULATEXT(G7)
I5,G28,L20,M17,L12,M9	I5	=FORMULATEXT(I6)
I6:J20	I6	=APIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))
G7:G19	G7	=PIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))
M10:R10	M10	=PIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))
L13:S14	L13	=APIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))
M18	M18	=PIVOT(,,E3:E28,LAMBDA(x,SUM(x)))
L21:M22	L21	=APIVOT(,,E3:E28,LAMBDA(x,SUM(x)))
G29:N43	G29	=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,SUM(x)))
Dynamic array formulas.

 

[Xlambda]

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

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1		sample array (not table)					Functionality PIVOT/APIVOT when v arguments is omitted
2		Team	Name	Product	Sales
3		Red	Amanda	Apple	559		=PIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))
4		Red	Thiago	Kiwi	40		↓↓							=APIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))
5		Red	Carol	Lemon	808		↓↓							PT	Apple	Kiwi	Lemon	Lime	Orange	Quince	GT
6		Blue	Daniel	Lime	200		0				0			Amanda	0				0		0
7		Red	Daniela	Apple	903				0		0			Bernando			0		0		0
8		Blue	Eduardo	Orange	656				0	0				Carol			0	0			0
9		Red	Gabriel	Lemon	404		0			0				Daniel	0			0			0
10		Blue	Helena	Lime	526		0				0			Daniela	0				0		0
11		Blue	Bernando	Orange	291				0		0			Eduardo			0		0		0
12		Red	Isabella	Apple	559				0	0				Gabriel			0	0			0
13		Blue	Juao Pedro	Orange	485		0			0				Helena	0			0			0
14		Red	Jose	Lemon	354		0				0			Isabella	0				0		0
15		Blue	Stephanie	Lime	270				0	0				Jose			0	0			0
16		Red	Thiago	Apple	693				0		0			Juao Pedro			0		0		0
17		Red	Amanda	Orange	235					0		0		Stephanie				0		0	0
18		Blue	Bernando	Lemon	610		0	0						Thiago	0	0					0
19		Red	Carol	Lime	619									GT	0	0	0	0	0	0
20		Blue	Daniel	Apple	612
21		Red	Daniela	Orange	596								Makes sense when values argument "v" is omitted to get a bunch of 0's										Compared with PT, this is how PT will look when no values clm is selected
22		Blue	Eduardo	Lemon	448								that with our functions, at least, can be counted.
23		Red	Gabriel	Lime	851									=APIVOT(C3:C28,D3:D28,,LAMBDA(x,COUNT(x)))										Column Labels
24		Blue	Helena	Apple	285									PT	Apple	Kiwi	Lemon	Lime	Orange	Quince	GT		Row Labels	Apple	Kiwi	Lemon	Lime	Orange	Quince	Grand Total
25		Red	Isabella	Orange	538									Amanda	1				1		2		Amanda
26		Blue	Juao Pedro	Lemon	484									Bernando			1		1		2		Bernando
27		Red	Jose	Lime	119									Carol			1	1			2		Carol
28		Blue	Stephanie	Quince	578									Daniel	1			1			2		Daniel
29														Daniela	1				1		2		Daniela
30														Eduardo			1		1		2		Eduardo
31														Gabriel			1	1			2		Gabriel
32														Helena	1			1			2		Helena
33														Isabella	1				1		2		Isabella
34														Jose			1	1			2		Jose
35														Juao Pedro			1		1		2		Juao Pedro
36														Stephanie				1		1	2		Stephanie
37														Thiago	1	1					2		Thiago
38														GT	6	1	6	6	6	1			Grand Total
39
40													Note: When "v" is omitted notice that we have no value for Grand Total general(bottom right corner)
41													To get the GT general for count we have to choose a column as values "v", if "v" clm has only text values will use as "fn" COUNTA,
42													if "v" has numeric values we can use as fn both COUNT or COUNTA
43														=APIVOT(C3:C28,D3:D28,B3:B28,LAMBDA(x,COUNTA(x)))									Count of Team	Column Labels
44														PT	Apple	Kiwi	Lemon	Lime	Orange	Quince	GT		Row Labels	Apple	Kiwi	Lemon	Lime	Orange	Quince	Grand Total
45														Amanda	1				1		2		Amanda	1				1		2
46														Bernando			1		1		2		Bernando			1		1		2
47														Carol			1	1			2		Carol			1	1			2
48														Daniel	1			1			2		Daniel	1			1			2
49														Daniela	1				1		2		Daniela	1				1		2
50														Eduardo			1		1		2		Eduardo			1		1		2
51														Gabriel			1	1			2		Gabriel			1	1			2
52														Helena	1			1			2		Helena	1			1			2
53														Isabella	1				1		2		Isabella	1				1		2
54														Jose			1	1			2		Jose			1	1			2
55														Juao Pedro			1		1		2		Juao Pedro			1		1		2
56														Stephanie				1		1	2		Stephanie				1		1	2
57														Thiago	1	1					2		Thiago	1	1					2
58														GT	6	1	6	6	6	1	26		Grand Total	6	1	6	6	6	1	26
59
PIV 3

Cell Formulas
Range	Formula
G3	G3	=FORMULATEXT(G6)
N4,N43,N23	N4	=FORMULATEXT(N5)
N5:U19	N5	=APIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))
G6:L18	G6	=PIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))
N24:U38	N24	=APIVOT(C3:C28,D3:D28,,LAMBDA(x,COUNT(x)))
N44:U58	N44	=APIVOT(C3:C28,D3:D28,B3:B28,LAMBDA(x,COUNTA(x)))
Dynamic array formulas.

 

[Xlambda]

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)), …......

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		sample array (no headers)
2							=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,x))
3		A	z	b	1		PT	a	b	c	d	GT
4		B	y	a	5		A		1	7,7	4	1,4,7,7
5		C	z	d	7		B	5,3,3		2	6,9	5,2,3,6,3,9
6		D	z	c	4		C		3,4,4	4,4	7	7,3,4,4,4,4
7		C	x	b	3		D	8	9,9	4		4,8,9,9
8		A	x	d	4		GT	5,8,3,3	1,3,4,9,4,9	4,2,7,4,7,4	7,4,6,9	1,5,7,4,3,4,8,2,7,4,4,9,3,6,7,4,4,9,3,9
9		D	y	a	8
10		B	x	c	2		=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,x))
11		A	y	c	7		PT	a	b	c	d	GT
12		C	z	c	4		A		z	y,y	x	z,x,y,y
13		C	z	b	4		B	y,x,x		x	z,z	y,x,x,z,x,z
14		D	y	b	9		C		x,z,z	z,z	z	z,x,z,z,z,z
15		B	x	a	3		D	y	y,y	z		z,y,y,y
16		B	z	d	6		GT	y,y,x,x	z,x,z,y,z,y	z,x,y,z,y,z	z,x,z,z	z,y,z,z,x,x,y,x,y,z,z,y,x,z,y,z,z,y,x,z		Note: Another advantage of using
17		A	y	c	7									lambda helper functions combo PIVOT/APIVOT is
18		C	z	c	4									that PT interface is not reliable when an array has no headers.
19		C	z	b	4		checking consistency of the results							It will consider first row as headers, therefore results will not be accurate.
20		D	y	b	9		=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,COUNTA(x)))							Count of 1	Column Labels
21		B	x	a	3		PT	a	b	c	d	GT		Row Labels	a	b	c	d	Grand Total
22		B	z	d	9		A		1	2	1	4		A			2	1	3
23							B	3		1	2	6		B	3		1	2	6
24							C		3	2	1	6		C		3	2	1	6
25							D	1	2	1		4		D	1	2	1		4
26							GT	4	6	6	4	20		Grand Total	4	5	6	4	19
27
28														APIVOT excluding first row:
29														=APIVOT(B4:B22,D4:D22,C4:C22,LAMBDA(x,COUNTA(x)))
30														PT	a	b	c	d	GT
31														A			2	1	3
32														B	3		1	2	6
33														C		3	2	1	6
34														D	1	2	1		4
35														GT	4	5	6	4	19
36
PIV 4

Cell Formulas
Range	Formula
G2,N29,G20,G10	G2	=FORMULATEXT(G3)
G3:L8	G3	=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,x))
G11:L16	G11	=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,x))
G21:L26	G21	=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,COUNTA(x)))
N30:S35	N30	=APIVOT(B4:B22,D4:D22,C4:C22,LAMBDA(x,COUNTA(x)))
Dynamic array formulas.

 

[Xlambda]

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.

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		sample array (no headers)					top 2 values including ties								APIVOT can handle array calculations for its arguments
2							fn,LARGE(x,{1,2})								all values >=5
3		A	z	b	1		=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,LARGE(x,{1,2})))								=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,x),"all >=5")
4		B	y	a	5		PT	a	b	c	d	GT			all >=5	a	b	c	d	GT
5		C	z	d	7		A		1	7,7	4	7,7			A			7,7		7,7
6		D	z	c	4		B	8,5		2	9,6	9,8			B	5,5,8			6,9	5,5,6,8,9
7		C	x	b	3		C		4,4	4,4	7	7,4			C				7	7
8		A	x	d	4		D	8	9,9	4		9,9			D	8	9,9			8,9,9
9		D	y	a	8		GT	8,8	9,9	7,7	9,7	9,9			GT	5,8,5,8	9,9	7,7	7,6,9	5,7,8,7,9,5,6,7,9,8,9
10		B	x	c	2
11		A	y	c	7		top 2 values excluding ties								values >=5
12		C	z	c	4		fn,LARGE(UNIQUE(x),{1,2})								=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,UNIQUE(x)),">=5")
13		C	z	b	4		PT	a	b	c	d	GT			>=5	a	b	c	d	GT
14		D	y	b	9		A		1	7	4	7,4			A			7		7
15		B	x	a	5		B	8,5		2	9,6	9,8			B	5,8			6,9	5,6,8,9
16		B	z	d	6		C		4,3	4	7	7,4			C				7	7
17		A	y	c	7		D	8	9	4		9,8			D	8	9			8,9
18		C	z	c	4		GT	8,5	9,4	7,4	9,7	9,8			GT	5,8	9	7	7,6,9	5,7,8,9,6
19		C	z	b	4
20		D	y	b	9		unique values distribution x,y,z
21		B	x	a	8		fn,SORT(UNIQUE(x))								count values >=5
22		B	z	d	9		=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,SORT(UNIQUE(x))),"xyz distrib")								=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,COUNT(x)),"cnt >=5")
23							xyz distrib	a	b	c	d	GT			cnt >=5	a	b	c	d	GT
24							A		z	y	x	x,y,z			A			2		2
25							B	x,y		x	z	x,y,z			B	3			2	5
26							C		x,z	z	z	x,z			C				1	1
27							D	y	y	z		y,z			D	1	2			3
28							GT	x,y	x,y,z	x,y,z	x,z	x,y,z			GT	4	2	2	3	11
29
30							sum distribution all a,b,c,d								sum distribution for a,b ; c and d cumulated as "others"
31							=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,SUM(x)),"sum")							=APIVOT(B3:B22,IF((D3:D22="a")+(D3:D22="b"),D3:D22,"other"),E3:E22,LAMBDA(x,SUM(x)),"a,b")
32							sum	a	b	c	d	GT			a,b	a	b	other	GT
33							A		1	14	4	19			A		1	18	19
34							B	18		2	15	35			B	18		17	35
35							C		11	8	7	26			C		11	15	26
36							D	8	18	4		30			D	8	18	4	30
37							GT	26	30	28	26	110			GT	26	30	54	110
38
PIV 5

Cell Formulas
Range	Formula
G3,G31,G22,O22,O12,O3	G3	=FORMULATEXT(G4)
G4:L9	G4	=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,LARGE(x,{1,2})))
O4:T9	O4	=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,x),"all >=5")
G13:L18	G13	=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,LARGE(UNIQUE(x),{1,2})))
O13:T18	O13	=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,UNIQUE(x)),">=5")
G23:L28	G23	=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,SORT(UNIQUE(x))),"xyz distrib")
O23:T28	O23	=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,COUNT(x)),"cnt >=5")
N31	N31	=FORMULATEXT(O32)
G32:L37	G32	=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,SUM(x)),"sum")
O32:S37	O32	=APIVOT(B3:B22,IF((D3:D22="a")+(D3:D22="b"),D3:D22,"other"),E3:E22,LAMBDA(x,SUM(x)),"a,b")
Dynamic array formulas.

 

[Xlambda]

Since APIVOT arguments can take array calculations, manipulating dates comes handy.
Check out Leila's PT techniques YT: Properly Handling Date Grouping in Excel Pivot Tables (Change Grouping, Get All dates)
workbook download link: Excel Pivot Table Date Grouping - Xelplus - Leila Gharani

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	sample table 600 rows					=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)),"Year-Month","Tot Orders")
2	Salesperson	Order ID	Order date	Order amount		↓				=SORT(UNIQUE(S[Salesperson]))
3	Jack Potter	3284	04-01-21	4686.243939		Year-Month	Tot Orders			DV formula
4	Bob Caldwell	3285	04-01-21	17702.12692		2021-(01)-Jan	567729.4435			Amy Trefl
5	Bob Caldwell	3286	05-01-21	8563.469132		2021-(02)-Feb	691142.9563			Anthony Grosse
6	Taj Shand	3287	06-01-21	20481.33025		2021-(03)-Mar	658502.6592			Archer Lamble
7	Jack Potter	3288	09-01-21	15780.99334		2021-(04)-Apr	495310.5648			Bob Caldwell
8	Justine Hoffer	3289	11-01-21	7270.794936		2021-(05)-May	458611.6175			Don Johnson
9	Sheridan Smith	3290	11-01-21	5840.169297		2021-(06)-Jun	373276.693			Hudson Hollinworth
10	Taj Shand	3291	11-01-21	15376.80714		2021-(07)-Jul	591065.3804			Hudson Onslow
11	Archer Lamble	3292	12-01-21	23132.98203		2021-(08)-Aug	457049.7136			Jack Potter
12	Hudson Hollinworth	3293	12-01-21	19249.77766		2021-(09)-Sep	766123.202			Justine Hoffer
13	Hudson Onslow	3294	12-01-21	18854.76098		2021-(10)-Oct	606950.1752			Kayla Woodcock
14	Justine Hoffer	3295	12-01-21	21544.81306		2021-(11)-Nov	690283.5764			Lily Code
15	Taj Shand	3296	13-01-21	23344.86717		2021-(12)-Dec	813876.6413			Peter Chowdhry
16	Don Johnson	3297	15-01-21	15420.93139		2022-(01)-Jan	730249.9767			Sheridan Smith
17	Amy Trefl	3298	16-01-21	15300.26447		2022-(02)-Feb	604319.1448			Sophia Hinton
18	Lily Code	3299	18-01-21	8091.068995		GT	8504491.745			Taj Shand
19	Hudson Onslow	3300	18-01-21	16146.70696
20	Bob Caldwell	3301	18-01-21	5858.817495
21	Kayla Woodcock	3302	19-01-21	18023.42206		=APIVOT(WEEKDAY(S[Order date],2)&"-"&TEXT(S[Order date],"ddd"),,S[Order amount],LAMBDA(x,SUM(x)),"SUM/wdays")
22	Sheridan Smith	3303	20-01-21	21212.95523		SUM/wdays	GT
23	Hudson Hollinworth	3304	21-01-21	6270.727547		1-Mon	2357492.283			Selection
24	Archer Lamble	3305	21-01-21	11684.54499		2-Tue	1259545.731			DV Salesperson
25	Sophia Hinton	3306	21-01-21	23250.86807		3-Wed	1133082.268			Archer Lamble
26	Hudson Onslow	3307	21-01-21	5584.412033		4-Thu	1137252.179			Lily Code
27	Hudson Hollinworth	3308	21-01-21	18973.50998		5-Fri	1257000.392
28	Kayla Woodcock	3309	23-01-21	15544.87942		6-Sat	1360118.893
29	Sophia Hinton	3310	25-01-21	9941.138917		GT	8504491.745
30	Lily Code	3311	25-01-21	5418.414547
31	Hudson Onslow	3312	26-01-21	16683.98206		=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),IF(ISNUMBER(XMATCH(S[Salesperson],J25:J26)),S[Salesperson],"Ꝋthers"),S[Order amount],LAMBDA(x,SUM(x)),"SUM Selection")
32	Amy Trefl	3313	28-01-21	21285.61157		SUM Selection	Archer Lamble	Lily Code	Ꝋthers	GT		=IFERROR(G33:G47+H33:H47,"")
33	Bob Caldwell	3314	28-01-21	17873.08037		2021-(01)-Jan	45797.30064	13509.48354	508422.6593	567729.4435		59306.78419
34	Amy Trefl	3315	29-01-21	18233.57937		2021-(02)-Feb	34954.92092	31727.6317	624460.4037	691142.9563		66682.55262
35	Amy Trefl	3316	29-01-21	20485.99442		2021-(03)-Mar	52455.91438	39902.49664	566144.2481	658502.6592		92358.41102
36	Archer Lamble	3317	29-01-21	10979.77362		2021-(04)-Apr		13984.29783	481326.267	495310.5648
37	Hudson Hollinworth	3318	29-01-21	16994.45554		2021-(05)-May	9768.432142	43178.77407	405664.4112	458611.6175		52947.20622
38	Justine Hoffer	3319	30-01-21	9521.601081		2021-(06)-Jun	22514.24341	10012.9606	340749.4889	373276.693		32527.20401
39	Bob Caldwell	3320	30-01-21	9560.260341		2021-(07)-Jul	34064.86321	101145.9112	455854.606	591065.3804		135210.7744
40	Sheridan Smith	3321	30-01-21	14020.60802		2021-(08)-Aug	14390.26149	58419.51281	384239.9393	457049.7136		72809.7743
41	Bob Caldwell	3322	30-01-21	13538.69912		2021-(09)-Sep	38575.42867		727547.7733	766123.202
42	Bob Caldwell	3323	01-02-21	21596.07058		2021-(10)-Oct	10239.04287	50764.67789	545946.4545	606950.1752		61003.72077
43	Sheridan Smith	3324	01-02-21	8228.796233		2021-(11)-Nov	70241.56882	22955.95445	597086.0531	690283.5764		93197.52327
44	Kayla Woodcock	3325	01-02-21	24965.01588		2021-(12)-Dec	50230.87472	126931.199	636714.5677	813876.6413		177162.0737
45	Don Johnson	3326	01-02-21	14049.72532		2022-(01)-Jan	30490.99175	59606.79675	640152.1882	730249.9767		90097.78851
46	Hudson Hollinworth	3327	01-02-21	13880.84707		2022-(02)-Feb	25638.85639	37906.4995	540773.7889	604319.1448		63545.35589
47	Justine Hoffer	3328	01-02-21	23031.95227		GT	439362.6994	610046.1959	7455082.849	8504491.745		1049408.895
48	Anthony Grosse	3329	01-02-21	22969.89558
49	Jack Potter	3330	02-02-21	13545.11058
50	Bob Caldwell	3331	02-02-21	6750.206713				Omega char	=UNICODE(H51)
51	Anthony Grosse	3332	02-02-21	11978.34044				Ꝋ	42826
52	Don Johnson	3333	02-02-21	14838.46588
PIV 6

Cell Formulas
Range	Formula
F1,J2	F1	=FORMULATEXT(F3)
F3:G18	F3	=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)),"Year-Month","Tot Orders")
J4:J18	J4	=SORT(UNIQUE(S[Salesperson]))
F21,I50,L32,F31	F21	=FORMULATEXT(F22)
F22:G29	F22	=APIVOT(WEEKDAY(S[Order date],2)&"-"&TEXT(S[Order date],"ddd"),,S[Order amount],LAMBDA(x,SUM(x)),"SUM/wdays")
F32:J47	F32	=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),IF(ISNUMBER(XMATCH(S[Salesperson],J25:J26)),S[Salesperson],"Ꝋthers"),S[Order amount],LAMBDA(x,SUM(x)),"SUM Selection")
L33:L47	L33	=IFERROR(G33:G47+H33:H47,"")
I51	I51	=UNICODE(H51)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J25:J29	List	=$J$4#

 

[Xlambda]

To solve Quarter grouping challenge here is QTR function. Capable of dealing also with fiscal quarters/years, not only with calendar ones.
Start month (s), of fiscal year by country can be different. Most common s's are 4,7,10.
The function uses by default s=4 (April) To change this value, change the first variable "s" , first variable declared after LET.
QTR(a,[qt])
a: dates array
[qt]: quarter type argument, text, could be one of these values:
"q": calendar quarter
"qy": calendar quarter and year, (year will be listed first for sorting versatility) ; format ex.: 2022 Q3
"fq": fiscal quarter : format ex.: FQ4
"fy": fiscal year ; format ex.: FY 2023 (represents fiscal year 2022-2023)
"fqy": fiscal quarter and year, (year will be listed first for sorting versatility) ; format ex.: FY 2022 Q2
- if qt is omitted, function will calculate "qy", if qt<> above values function returns #NA() error

=LAMBDA(a, [qt],
    LET(
        s, 4,
        m, MONTH(a),
        y, YEAR(a),
        q, "Q" & MONTH(m & 0),
        qy, y & " " & q,
        fq, "Q" & MONTH(MOD(m - s, 12) + 1 & 0),
        fy, "FY " & y + (m >= s),
        fqy, fy & " " & fq,
        SWITCH(qt, 0, qy, "q", q, "qy", qy, "fq", "F" & fq, "fy", fy, "fqy", fqy)
    )
)

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	QTR(a,[qt]). The function.
2																wrong argument
3	=DATE(2021,SEQUENCE(24),1)			qt,omitted		qt,"q"		qt,"qy"		qt,"fq"		qt,"fy"		qt,"fqy"		qt,"fyq"
4		↓↓↓		=QTR(B5#)		=QTR(B5#,"q")		=QTR(B5#,"qy")		=QTR(B5#,"fq")		=QTR(B5#,"fy")		=QTR(B5#,"fqy")		=QTR(B5#,"fyq")
5		01-01-21		2021 Q1		Q1		2021 Q1		FQ4		FY 2021		FY 2021 Q4		#N/A
6		01-02-21		2021 Q1		Q1		2021 Q1		FQ4		FY 2021		FY 2021 Q4
7		01-03-21		2021 Q1		Q1		2021 Q1		FQ4		FY 2021		FY 2021 Q4
8		01-04-21		2021 Q2		Q2		2021 Q2		FQ1		FY 2022		FY 2022 Q1
9		01-05-21		2021 Q2		Q2		2021 Q2		FQ1		FY 2022		FY 2022 Q1
10		01-06-21		2021 Q2		Q2		2021 Q2		FQ1		FY 2022		FY 2022 Q1
11		01-07-21		2021 Q3		Q3		2021 Q3		FQ2		FY 2022		FY 2022 Q2
12		01-08-21		2021 Q3		Q3		2021 Q3		FQ2		FY 2022		FY 2022 Q2
13		01-09-21		2021 Q3		Q3		2021 Q3		FQ2		FY 2022		FY 2022 Q2
14		01-10-21		2021 Q4		Q4		2021 Q4		FQ3		FY 2022		FY 2022 Q3
15		01-11-21		2021 Q4		Q4		2021 Q4		FQ3		FY 2022		FY 2022 Q3
16		01-12-21		2021 Q4		Q4		2021 Q4		FQ3		FY 2022		FY 2022 Q3
17		01-01-22		2022 Q1		Q1		2022 Q1		FQ4		FY 2022		FY 2022 Q4
18		01-02-22		2022 Q1		Q1		2022 Q1		FQ4		FY 2022		FY 2022 Q4
19		01-03-22		2022 Q1		Q1		2022 Q1		FQ4		FY 2022		FY 2022 Q4
20		01-04-22		2022 Q2		Q2		2022 Q2		FQ1		FY 2023		FY 2023 Q1
21		01-05-22		2022 Q2		Q2		2022 Q2		FQ1		FY 2023		FY 2023 Q1
22		01-06-22		2022 Q2		Q2		2022 Q2		FQ1		FY 2023		FY 2023 Q1
23		01-07-22		2022 Q3		Q3		2022 Q3		FQ2		FY 2023		FY 2023 Q2
24		01-08-22		2022 Q3		Q3		2022 Q3		FQ2		FY 2023		FY 2023 Q2
25		01-09-22		2022 Q3		Q3		2022 Q3		FQ2		FY 2023		FY 2023 Q2
26		01-10-22		2022 Q4		Q4		2022 Q4		FQ3		FY 2023		FY 2023 Q3
27		01-11-22		2022 Q4		Q4		2022 Q4		FQ3		FY 2023		FY 2023 Q3
28		01-12-22		2022 Q4		Q4		2022 Q4		FQ3		FY 2023		FY 2023 Q3
29
PIV 7

Cell Formulas
Range	Formula
A3	A3	=FORMULATEXT(B5)
F4,H4,J4,L4,N4,P4,D4	F4	=FORMULATEXT(F5)
B5:B28	B5	=DATE(2021,SEQUENCE(24),1)
D5:D28	D5	=QTR(B5#)
F5:F28	F5	=QTR(B5#,"q")
H5:H28	H5	=QTR(B5#,"qy")
J5:J28	J5	=QTR(B5#,"fq")
L5:L28	L5	=QTR(B5#,"fy")
N5:N28	N5	=QTR(B5#,"fqy")
P5	P5	=QTR(B5#,"fyq")
Dynamic array formulas.

 

[Xlambda]

Fiscal/Calendar year/quarter reports with APIVOT.

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	sample table 600 rows					=APIVOT(QTR(S[Order date]),,S[Order amount],LAMBDA(x,SUM(x)))
2	Salesperson	Order ID	Order date	Order amount		↓↓↓			=APIVOT(QTR(S[Order date],"q"),,S[Order amount],LAMBDA(x,SUM(x)))
3	Jack Potter	3284	04-01-21	4686.243939		↓↓↓			↓↓↓			Pivot table check
4	Bob Caldwell	3285	04-01-21	17702.12692		PT	GT		PT	GT		Row Labels	Sum of Order amount
5	Bob Caldwell	3286	05-01-21	8563.469132		2021 Q1	1917375.059		Q1	3251944.18		Qtr1	3251944.18
6	Taj Shand	3287	06-01-21	20481.33025		2021 Q2	1327198.875		Q2	1327198.875		Qtr2	1327198.875
7	Jack Potter	3288	09-01-21	15780.99334		2021 Q3	1814238.296		Q3	1814238.296		Qtr3	1814238.296
8	Justine Hoffer	3289	11-01-21	7270.794936		2021 Q4	2111110.393		Q4	2111110.393		Qtr4	2111110.393
9	Sheridan Smith	3290	11-01-21	5840.169297		2022 Q1	1334569.122		GT	8504491.745		Grand Total	8504491.745
10	Taj Shand	3291	11-01-21	15376.80714		GT	8504491.745
11	Archer Lamble	3292	12-01-21	23132.98203
12	Hudson Hollinworth	3293	12-01-21	19249.77766		=APIVOT(QTR(S[Order date],"fq"),,S[Order amount],LAMBDA(x,SUM(x)))
13	Hudson Onslow	3294	12-01-21	18854.76098		PT	GT
14	Justine Hoffer	3295	12-01-21	21544.81306		FQ1	1327198.875		=APIVOT(QTR(S[Order date],"fy"),,S[Order amount],LAMBDA(x,SUM(x)))
15	Taj Shand	3296	13-01-21	23344.86717		FQ2	1814238.296		PT	GT
16	Don Johnson	3297	15-01-21	15420.93139		FQ3	2111110.393		FY 2021	1917375.059
17	Amy Trefl	3298	16-01-21	15300.26447		FQ4	3251944.18		FY 2022	6587116.686		Pivot Table check (values F4:G10)
18	Lily Code	3299	18-01-21	8091.068995		GT	8504491.745		GT	8504491.745		Row Labels	Sum of Order amount
19	Hudson Onslow	3300	18-01-21	16146.70696								2021
20	Bob Caldwell	3301	18-01-21	5858.817495		=APIVOT(QTR(S[Order date],"fqy"),,S[Order amount],LAMBDA(x,SUM(x)))						Qtr1	1917375.059
21	Kayla Woodcock	3302	19-01-21	18023.42206		PT	GT					Qtr2	1327198.875
22	Sheridan Smith	3303	20-01-21	21212.95523		FY 2021 Q4	1917375.059					Qtr3	1814238.296
23	Hudson Hollinworth	3304	21-01-21	6270.727547		FY 2022 Q1	1327198.875					Qtr4	2111110.393
24	Archer Lamble	3305	21-01-21	11684.54499		FY 2022 Q2	1814238.296					2022
25	Sophia Hinton	3306	21-01-21	23250.86807		FY 2022 Q3	2111110.393					Qtr1	1334569.122
26	Hudson Onslow	3307	21-01-21	5584.412033		FY 2022 Q4	1334569.122					Grand Total	8504491.745
27	Hudson Hollinworth	3308	21-01-21	18973.50998		GT	8504491.745
28	Kayla Woodcock	3309	23-01-21	15544.87942
29	Sophia Hinton	3310	25-01-21	9941.138917		Checking quarter values summing correspondent month
30	Lily Code	3311	25-01-21	5418.414547		=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)))
31	Hudson Onslow	3312	26-01-21	16683.98206		PT	GT					Pivot table check
32	Amy Trefl	3313	28-01-21	21285.61157		21-(01)-Jan	567729.4435					Row Labels	Sum of Order amount
33	Bob Caldwell	3314	28-01-21	17873.08037		21-(02)-Feb	691142.9563		=SUM(G32:G34)=G5			2021
34	Amy Trefl	3315	29-01-21	18233.57937		21-(03)-Mar	658502.6592		TRUE			Jan	567729.4435
35	Amy Trefl	3316	29-01-21	20485.99442		21-(04)-Apr	495310.5648					Feb	691142.9563
36	Archer Lamble	3317	29-01-21	10979.77362		21-(05)-May	458611.6175		=SUM(G35:G37)=G6			Mar	658502.6592
37	Hudson Hollinworth	3318	29-01-21	16994.45554		21-(06)-Jun	373276.693		TRUE			Apr	495310.5648
38	Justine Hoffer	3319	30-01-21	9521.601081		21-(07)-Jul	591065.3804					May	458611.6175
39	Bob Caldwell	3320	30-01-21	9560.260341		21-(08)-Aug	457049.7136		=SUM(G38:G40)=G7			Jun	373276.693
40	Sheridan Smith	3321	30-01-21	14020.60802		21-(09)-Sep	766123.202		TRUE			Jul	591065.3804
41	Bob Caldwell	3322	30-01-21	13538.69912		21-(10)-Oct	606950.1752					Aug	457049.7136
42	Bob Caldwell	3323	01-02-21	21596.07058		21-(11)-Nov	690283.5764		=SUM(G41:G43)=G8			Sep	766123.202
43	Sheridan Smith	3324	01-02-21	8228.796233		21-(12)-Dec	813876.6413		TRUE			Oct	606950.1752
44	Kayla Woodcock	3325	01-02-21	24965.01588		22-(01)-Jan	730249.9767		=SUM(G44:G45)=G9			Nov	690283.5764
45	Don Johnson	3326	01-02-21	14049.72532		22-(02)-Feb	604319.1448		TRUE			Dec	813876.6413
46	Hudson Hollinworth	3327	01-02-21	13880.84707		GT	8504491.745					2022
47	Justine Hoffer	3328	01-02-21	23031.95227								Jan	730249.9767
48	Anthony Grosse	3329	01-02-21	22969.89558								Feb	604319.1448
49	Jack Potter	3330	02-02-21	13545.11058		table rows						Grand Total	8504491.745
50	Bob Caldwell	3331	02-02-21	6750.206713		=ROWS(S)
51	Anthony Grosse	3332	02-02-21	11978.34044		600
52	Don Johnson	3333	02-02-21	14838.46588
PIV 8

Cell Formulas
Range	Formula
F1	F1	=FORMULATEXT(F4)
I2	I2	=FORMULATEXT(I4)
F4:G10	F4	=APIVOT(QTR(S[Order date]),,S[Order amount],LAMBDA(x,SUM(x)))
I4:J9	I4	=APIVOT(QTR(S[Order date],"q"),,S[Order amount],LAMBDA(x,SUM(x)))
F12,F50,I44,I42,I39,I36,I33,F30,F20,I14	F12	=FORMULATEXT(F13)
F13:G18	F13	=APIVOT(QTR(S[Order date],"fq"),,S[Order amount],LAMBDA(x,SUM(x)))
I15:J18	I15	=APIVOT(QTR(S[Order date],"fy"),,S[Order amount],LAMBDA(x,SUM(x)))
F21:G27	F21	=APIVOT(QTR(S[Order date],"fqy"),,S[Order amount],LAMBDA(x,SUM(x)))
F31:G46	F31	=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)))
I34	I34	=SUM(G32:G34)=G5
I37	I37	=SUM(G35:G37)=G6
I40	I40	=SUM(G38:G40)=G7
I43	I43	=SUM(G41:G43)=G8
I45	I45	=SUM(G44:G45)=G9
F51	F51	=ROWS(S)
Dynamic array formulas.

 

[Xlambda]

Task: Report top/last 3 Order amounts values per each month for each Salesperson.

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	sample table 600 rows
2	Salesperson	Order ID	Order date	Order amount
3	Jack Potter	3284	04-01-21	4686.243939
4	Bob Caldwell	3285	04-01-21	17702.12692		=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,LARGE(INT(x),SEQUENCE(3))),"Top 3/mnth","top 3","top 3 all time")
5	Bob Caldwell	3286	05-01-21	8563.469132		Top 3/mnth	top 3
6	Taj Shand	3287	06-01-21	20481.33025		21-(01)-January	23344, 23250, 23132
7	Jack Potter	3288	09-01-21	15780.99334		21-(02)-February	24965, 24304, 23126
8	Justine Hoffer	3289	11-01-21	7270.794936		21-(03)-March	24517, 24219, 23842
9	Sheridan Smith	3290	11-01-21	5840.169297		21-(04)-April	24585, 23927, 23855
10	Taj Shand	3291	11-01-21	15376.80714		21-(05)-May	23970, 22518, 19514
11	Archer Lamble	3292	12-01-21	23132.98203		21-(06)-June	24594, 24413, 22697
12	Hudson Hollinworth	3293	12-01-21	19249.77766		21-(07)-July	24720, 24162, 23942
13	Hudson Onslow	3294	12-01-21	18854.76098		21-(08)-August	24100, 24004, 23602
14	Justine Hoffer	3295	12-01-21	21544.81306		21-(09)-September	23455, 23261, 23227
15	Taj Shand	3296	13-01-21	23344.86717		21-(10)-October	24939, 23697, 23659
16	Don Johnson	3297	15-01-21	15420.93139		21-(11)-November	24714, 24282, 24245
17	Amy Trefl	3298	16-01-21	15300.26447		21-(12)-December	23874, 23868, 23514
18	Lily Code	3299	18-01-21	8091.068995		22-(01)-January	24782, 24197, 23916
19	Hudson Onslow	3300	18-01-21	16146.70696		22-(02)-February	24999, 24097, 23445
20	Bob Caldwell	3301	18-01-21	5858.817495		top 3 all time	24999, 24965, 24939
21	Kayla Woodcock	3302	19-01-21	18023.42206
22	Sheridan Smith	3303	20-01-21	21212.95523
23	Hudson Hollinworth	3304	21-01-21	6270.727547		=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,SMALL(INT(x),SEQUENCE(3))),"Last 3/mnth","last 3","last 3 all time")
24	Archer Lamble	3305	21-01-21	11684.54499		Last 3/mnth	last 3
25	Sophia Hinton	3306	21-01-21	23250.86807		21-(01)-January	4686, 5418, 5584
26	Hudson Onslow	3307	21-01-21	5584.412033		21-(02)-February	4070, 4580, 4901
27	Hudson Hollinworth	3308	21-01-21	18973.50998		21-(03)-March	4113, 4426, 4502
28	Kayla Woodcock	3309	23-01-21	15544.87942		21-(04)-April	4589, 5057, 5174
29	Sophia Hinton	3310	25-01-21	9941.138917		21-(05)-May	4304, 4745, 4873
30	Lily Code	3311	25-01-21	5418.414547		21-(06)-June	4776, 5091, 5236
31	Hudson Onslow	3312	26-01-21	16683.98206		21-(07)-July	5204, 6209, 6743
32	Amy Trefl	3313	28-01-21	21285.61157		21-(08)-August	4731, 5012, 5385
33	Bob Caldwell	3314	28-01-21	17873.08037		21-(09)-September	4428, 4430, 4783
34	Amy Trefl	3315	29-01-21	18233.57937		21-(10)-October	4018, 4841, 5100
35	Amy Trefl	3316	29-01-21	20485.99442		21-(11)-November	4319, 4546, 5049
36	Archer Lamble	3317	29-01-21	10979.77362		21-(12)-December	4259, 4279, 4396
37	Hudson Hollinworth	3318	29-01-21	16994.45554		22-(01)-January	4024, 4113, 4464
38	Justine Hoffer	3319	30-01-21	9521.601081		22-(02)-February	4189, 4398, 7060
39	Bob Caldwell	3320	30-01-21	9560.260341		last 3 all time	4018, 4024, 4070
40	Sheridan Smith	3321	30-01-21	14020.60802
41	Bob Caldwell	3322	30-01-21	13538.69912
PIV 9

Cell Formulas
Range	Formula
F4,F23	F4	=FORMULATEXT(F5)
F5:G20	F5	=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,LARGE(INT(x),SEQUENCE(3))),"Top 3/mnth","top 3","top 3 all time")
F24:G39	F24	=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,SMALL(INT(x),SEQUENCE(3))),"Last 3/mnth","last 3","last 3 all time")
Dynamic array formulas.

 

[Xlambda]

Task 1: Total nr. of Orders registered/Salesperson entire period.
Task 2: How many Salespersons registered at least one order each month of every year.
Task 3: What Salespersons did not register any Order per month and year.

LHPIVOT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	sample table 600 rows
2	Salesperson	Order ID	Order date	Order amount
3	Jack Potter	3284	04-01-21	4686.243939		Task 1
4	Bob Caldwell	3285	04-01-21	17702.12692		=APIVOT(S[Salesperson],,S[Order amount],LAMBDA(x,COUNT(x)))
5	Bob Caldwell	3286	05-01-21	8563.469132		↓↓↓
6	Taj Shand	3287	06-01-21	20481.33025		↓↓↓			Task 3
7	Jack Potter	3288	09-01-21	15780.99334		PT	GT		=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,FILTER(F8:F22,ISNA(XMATCH(F8:F22,SORT(UNIQUE(x)))))))
8	Justine Hoffer	3289	11-01-21	7270.794936		Amy Trefl	35		PT	GT
9	Sheridan Smith	3290	11-01-21	5840.169297		Anthony Grosse	43		21 (01) Jan	Anthony Grosse, Peter Chowdhry
10	Taj Shand	3291	11-01-21	15376.80714		Archer Lamble	34		21 (02) Feb
11	Archer Lamble	3292	12-01-21	23132.98203		Bob Caldwell	42		21 (03) Mar
12	Hudson Hollinworth	3293	12-01-21	19249.77766		Don Johnson	46		21 (04) Apr	Archer Lamble, Sophia Hinton
13	Hudson Onslow	3294	12-01-21	18854.76098		Hudson Hollinworth	40		21 (05) May	Sophia Hinton
14	Justine Hoffer	3295	12-01-21	21544.81306		Hudson Onslow	34		21 (06) Jun	Justine Hoffer
15	Taj Shand	3296	13-01-21	23344.86717		Jack Potter	29		21 (07) Jul	Jack Potter
16	Don Johnson	3297	15-01-21	15420.93139		Justine Hoffer	55		21 (08) Aug	Amy Trefl
17	Amy Trefl	3298	16-01-21	15300.26447		Kayla Woodcock	39		21 (09) Sep	Lily Code
18	Lily Code	3299	18-01-21	8091.068995		Lily Code	41		21 (10) Oct
19	Hudson Onslow	3300	18-01-21	16146.70696		Peter Chowdhry	35		21 (11) Nov	Hudson Onslow
20	Bob Caldwell	3301	18-01-21	5858.817495		Sheridan Smith	43		21 (12) Dec
21	Kayla Woodcock	3302	19-01-21	18023.42206		Sophia Hinton	34		22 (01) Jan
22	Sheridan Smith	3303	20-01-21	21212.95523		Taj Shand	50		22 (02) Feb	Kayla Woodcock, Peter Chowdhry
23	Hudson Hollinworth	3304	21-01-21	6270.727547		GT	600		GT
24	Archer Lamble	3305	21-01-21	11684.54499
25	Sophia Hinton	3306	21-01-21	23250.86807
26	Hudson Onslow	3307	21-01-21	5584.412033		Task 2
27	Hudson Hollinworth	3308	21-01-21	18973.50998		=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,COUNTA(SORT(UNIQUE(x)))))
28	Kayla Woodcock	3309	23-01-21	15544.87942		PT	GT
29	Sophia Hinton	3310	25-01-21	9941.138917		21 (01) Jan	13
30	Lily Code	3311	25-01-21	5418.414547		21 (02) Feb	15
31	Hudson Onslow	3312	26-01-21	16683.98206		21 (03) Mar	15
32	Amy Trefl	3313	28-01-21	21285.61157		21 (04) Apr	13
33	Bob Caldwell	3314	28-01-21	17873.08037		21 (05) May	14
34	Amy Trefl	3315	29-01-21	18233.57937		21 (06) Jun	14
35	Amy Trefl	3316	29-01-21	20485.99442		21 (07) Jul	14
36	Archer Lamble	3317	29-01-21	10979.77362		21 (08) Aug	14
37	Hudson Hollinworth	3318	29-01-21	16994.45554		21 (09) Sep	14
38	Justine Hoffer	3319	30-01-21	9521.601081		21 (10) Oct	15
39	Bob Caldwell	3320	30-01-21	9560.260341		21 (11) Nov	14
40	Sheridan Smith	3321	30-01-21	14020.60802		21 (12) Dec	15
41	Bob Caldwell	3322	30-01-21	13538.69912		22 (01) Jan	15
42	Bob Caldwell	3323	01-02-21	21596.07058		22 (02) Feb	13
43	Sheridan Smith	3324	01-02-21	8228.796233		GT	15
44	Kayla Woodcock	3325	01-02-21	24965.01588
45	Don Johnson	3326	01-02-21	14049.72532
PIV 10

Cell Formulas
Range	Formula
F4	F4	=FORMULATEXT(F7)
F7:G23	F7	=APIVOT(S[Salesperson],,S[Order amount],LAMBDA(x,COUNT(x)))
I7,F27	I7	=FORMULATEXT(I8)
I8:J23	I8	=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,FILTER(F8:F22,ISNA(XMATCH(F8:F22,SORT(UNIQUE(x)))))))
F28:G43	F28	=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,COUNTA(SORT(UNIQUE(x)))))
Dynamic array formulas.