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]

Output.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	P&L Item	Year	Value	Name		=LET(a,VSTACK([S1.xlsx]A!$A$20#,[S2.xlsx]A!$A$21#,[S3.xlsx]A!$A$20#),FILTER(a,ISNUMBER(INDEX(a,,2))))
2	SALES	2022	7665499.64	P&L_Store1.xlsx		SALES	2022	7665499.64	Store 1
3	SALES	2021	6877877.23	P&L_Store1.xlsx		SALES	2021	6877877.23	Store 1		Using APIVOT to check PQ's results and ours. Will check not the sums but the averages
4	SALES	2020	7915191.08	P&L_Store1.xlsx		SALES	2020	7915191.08	Store 1
5	SALES	2019	6390653.09	P&L_Store1.xlsx		SALES	2019	6390653.09	Store 1		=APIVOT(Table_Clean_up_P_L[Year],Table_Clean_up_P_L[Name],Table_Clean_up_P_L[Value],LAMBDA(x,AVERAGE(x)))
6	SALES	2018	6180665.04	P&L_Store1.xlsx		SALES	2018	6180665.04	Store 1		PT	P&L_Store1.xlsx	P&L_Store2.xlsx	P&L_Store3.xlsx	GT
7	SALES	2017	6522702.84	P&L_Store1.xlsx		SALES	2017	6522702.84	Store 1		2006	98375.67			98375.67
8	SALES	2016	7407254.81	P&L_Store1.xlsx		SALES	2016	7407254.81	Store 1		2007	128632.428			128632.428
9	SALES	2015	7881384.08	P&L_Store1.xlsx		SALES	2015	7881384.08	Store 1		2008	138135.613		73866.31444	107692.2611
10	SALES	2014	7900683.67	P&L_Store1.xlsx		SALES	2014	7900683.67	Store 1		2009	338168.923		86334.451	212251.687
11	SALES	2013	7623621.69	P&L_Store1.xlsx		SALES	2013	7623621.69	Store 1		2010	368156.79	59078.92222	88482.37	175796.6172
12	SALES	2012	7474840.01	P&L_Store1.xlsx		SALES	2012	7474840.01	Store 1		2011	517213.445	164295.912	240583.817	307364.3913
13	SALES	2011	6046665.4	P&L_Store1.xlsx		SALES	2011	6046665.4	Store 1		2012	526039.295	223736.02	279919.428	343231.581
14	SALES	2010	4797450.5	P&L_Store1.xlsx		SALES	2010	4797450.5	Store 1		2013	568315.928	282451.934	239269.935	363345.9323
15	SALES	2009	4238988.64	P&L_Store1.xlsx		SALES	2009	4238988.64	Store 1		2014	601034.076	267849.929	188468.49	352450.8317
16	SALES	2008	2569998.94	P&L_Store1.xlsx		SALES	2008	2569998.94	Store 1		2015	575157.518	204633.659	147317.753	309036.31
17	SALES	2007	2107365.63	P&L_Store1.xlsx		SALES	2007	2107365.63	Store 1		2016	557595.347	175940.192	150056.825	294530.788
18	SALES	2006	1780543.73	P&L_Store1.xlsx		SALES	2006	1780543.73	Store 1		2017	478038.663	190468.926	114964.91	261157.4997
19	LESS: COST OF SALES	2022	-4727358.71	P&L_Store1.xlsx		LESS: COST OF SALES	2022	-4727358.71	Store 1		2018	439402.582	177560.821	145965.875	254309.7593
20	LESS: COST OF SALES	2021	-4351331.3	P&L_Store1.xlsx		LESS: COST OF SALES	2021	-4351331.3	Store 1		2019	586646.8063	241606.225	285194.0363	371149.0225
21	LESS: COST OF SALES	2020	-5184178.21	P&L_Store1.xlsx		LESS: COST OF SALES	2020	-5184178.21	Store 1		2020	676173.9855	220138.6845	373994.3655	423435.6785
22	LESS: COST OF SALES	2019	-4163713.07	P&L_Store1.xlsx		LESS: COST OF SALES	2019	-4163713.07	Store 1		2021	493091.2682	147973.4955	197285.3173	279450.027
23	LESS: COST OF SALES	2018	-4075940.66	P&L_Store1.xlsx		LESS: COST OF SALES	2018	-4075940.66	Store 1		2022	610066.7555	110931.0636	173373.1645	298123.6612
24	LESS: COST OF SALES	2017	-4372549.45	P&L_Store1.xlsx		LESS: COST OF SALES	2017	-4372549.45	Store 1		GT	455941.8451	189256.9978	186341.1265	289032.6497
25	LESS: COST OF SALES	2016	-5086478.49	P&L_Store1.xlsx		LESS: COST OF SALES	2016	-5086478.49	Store 1
26	LESS: COST OF SALES	2015	-5435427.23	P&L_Store1.xlsx		LESS: COST OF SALES	2015	-5435427.23	Store 1		=APIVOT(G2:G451,I2:I451,H2:H451,LAMBDA(x,AVERAGE(x)))
27	LESS: COST OF SALES	2014	-5512907.95	P&L_Store1.xlsx		LESS: COST OF SALES	2014	-5512907.95	Store 1		PT	Store 1	Store 2	Store 3	GT
28	LESS: COST OF SALES	2013	-5389529.74	P&L_Store1.xlsx		LESS: COST OF SALES	2013	-5389529.74	Store 1		2006	98375.67			98375.67
29	LESS: COST OF SALES	2012	-5366470.63	P&L_Store1.xlsx		LESS: COST OF SALES	2012	-5366470.63	Store 1		2007	128632.428			128632.428
30	LESS: COST OF SALES	2011	-4104185.74	P&L_Store1.xlsx		LESS: COST OF SALES	2011	-4104185.74	Store 1		2008	138135.613		73866.31444	107692.2611
31	LESS: COST OF SALES	2010	-3289682.02	P&L_Store1.xlsx		LESS: COST OF SALES	2010	-3289682.02	Store 1		2009	338168.923		86334.451	212251.687
32	LESS: COST OF SALES	2009	-2923228.32	P&L_Store1.xlsx		LESS: COST OF SALES	2009	-2923228.32	Store 1		2010	368156.79	59078.92222	88482.37	175796.6172
33	LESS: COST OF SALES	2008	-1858275.51	P&L_Store1.xlsx		LESS: COST OF SALES	2008	-1858275.51	Store 1		2011	517213.445	164295.912	240583.817	307364.3913
34	LESS: COST OF SALES	2007	-1487724.66	P&L_Store1.xlsx		LESS: COST OF SALES	2007	-1487724.66	Store 1		2012	526039.295	223736.02	279919.428	343231.581
35	LESS: COST OF SALES	2006	-1302388.62	P&L_Store1.xlsx		LESS: COST OF SALES	2006	-1302388.62	Store 1		2013	568315.928	282451.934	239269.935	363345.9323
36	GROSS PROFIT	2022	2938140.93	P&L_Store1.xlsx		GROSS PROFIT	2022	2938140.93	Store 1		2014	601034.076	267849.929	188468.49	352450.8317
37	GROSS PROFIT	2021	2526545.93	P&L_Store1.xlsx		GROSS PROFIT	2021	2526545.93	Store 1		2015	575157.518	204633.659	147317.753	309036.31
38	GROSS PROFIT	2020	2731012.87	P&L_Store1.xlsx		GROSS PROFIT	2020	2731012.87	Store 1		2016	557595.347	175940.192	150056.825	294530.788
39	GROSS PROFIT	2019	2226940.02	P&L_Store1.xlsx		GROSS PROFIT	2019	2226940.02	Store 1		2017	478038.663	190468.926	114964.91	261157.4997
40	GROSS PROFIT	2018	2104724.38	P&L_Store1.xlsx		GROSS PROFIT	2018	2104724.38	Store 1		2018	439402.582	177560.821	145965.875	254309.7593
41	GROSS PROFIT	2017	2150153.39	P&L_Store1.xlsx		GROSS PROFIT	2017	2150153.39	Store 1		2019	586646.8063	241606.225	285194.0363	371149.0225
42	GROSS PROFIT	2016	2320776.32	P&L_Store1.xlsx		GROSS PROFIT	2016	2320776.32	Store 1		2020	676173.9855	220138.6845	373994.3655	423435.6785
43	GROSS PROFIT	2015	2445956.85	P&L_Store1.xlsx		GROSS PROFIT	2015	2445956.85	Store 1		2021	493091.2682	147973.4955	197285.3173	279450.027
44	GROSS PROFIT	2014	2387775.72	P&L_Store1.xlsx		GROSS PROFIT	2014	2387775.72	Store 1		2022	610066.7555	110931.0636	173373.1645	298123.6612
45	GROSS PROFIT	2013	2234091.95	P&L_Store1.xlsx		GROSS PROFIT	2013	2234091.95	Store 1		GT	455941.8451	189256.9978	186341.1265	289032.6497
46	GROSS PROFIT	2012	2108369.38	P&L_Store1.xlsx		GROSS PROFIT	2012	2108369.38	Store 1
47	GROSS PROFIT	2011	1942479.66	P&L_Store1.xlsx		GROSS PROFIT	2011	1942479.66	Store 1
48	GROSS PROFIT	2010	1507768.48	P&L_Store1.xlsx		GROSS PROFIT	2010	1507768.48	Store 1			=AND(L7:O24=L28:O45)
49	GROSS PROFIT	2009	1315760.32	P&L_Store1.xlsx		GROSS PROFIT	2009	1315760.32	Store 1			TRUE
50	GROSS PROFIT	2008	711723.43	P&L_Store1.xlsx		GROSS PROFIT	2008	711723.43	Store 1
Clean up P&L

Cell Formulas
Range	Formula
F1,L48,K26,K5	F1	=FORMULATEXT(F2)
F2:I451	F2	=LET(a,VSTACK([S1.xlsx]A!$A$20#,[S2.xlsx]A!$A$21#,[S3.xlsx]A!$A$20#),FILTER(a,ISNUMBER(INDEX(a,,2))))
K6:O24	K6	=APIVOT(Table_Clean_up_P_L[Year],Table_Clean_up_P_L[Name],Table_Clean_up_P_L[Value],LAMBDA(x,AVERAGE(x)))
K27:O45	K27	=APIVOT(G2:G451,I2:I451,H2:H451,LAMBDA(x,AVERAGE(x)))
L49	L49	=AND(L7:O24=L28:O45)
Dynamic array formulas.

 

[Xlambda]

Some cool ideas inspired by latest YT from Chandoo

(has link to download the source workbook "monthly-workbook.xlsx")
Introducing a useful lambda: REF3D(vs,[hd]) a function that can deal with 3D references/ranges
vs: the vstack formula that can take 3D references
hd: headers argument; if 0 or omitted, no headers; if 1, we have headers

=LAMBDA(vs, [hd],
    LET(
        l, LAMBDA(x, AND(x = 0)),
        a, BYROW(vs, l),
        b, BYCOL(vs, l),
        f, FILTER(FILTER(vs, NOT(a)), NOT(b)),
        IF(hd, LET(h, TAKE(f, 1), g, BYROW(N(f = h), l), VSTACK(h, FILTER(f, g))), f)
    )
)

If source workbook is open, we add an empty spreadsheet at the end called "End" then we can call this lambda on any other workbook to create the stacked array:
=REF3D(VSTACK('[monthly-workbook.xlsx]Jan:End'!$A$1:$G$1000),1)
- as 3D reference we used larger array vertically and horizontally. The function is designed to crop the blanks in both directions.
- any spreadsheet added on source workbook before "End", updates automatically, no refresh needed

REF3D.xlsx
	A	B	C	D	E	F	G	H	I
1	Sales Person	Country	Product	Date	Amount	Boxes
2	Ches Bonnell	Australia	Choco Coated Almonds	44928	12971	721
3	Roddy Speechley	UK	70% Dark Bites	44928	1610	68		rows check
4	Rafaelita Blaksland	New Zealand	70% Dark Bites	44928	518	18		=ROWS(A1#)-1
5	Roddy Speechley	New Zealand	Mint Chip Choco	44928	1393	45		2751
6	Wilone O'Kielt	New Zealand	Raspberry Choco	44928	1064	51
7	Ches Bonnell	USA	Peanut Butter Cubes	44929	1988	111
8	Oby Sorrel	India	Choco Coated Almonds	44929	2457	145
9	Camilla Castle	Australia	Mint Chip Choco	44929	11158	360
10	Marney O'Breen	UK	Fruit & Nut Bars	44929	2961	198
11	Mallorie Waber	UK	Spicy Special Slims	44929	3738	340
12	Beverie Moffet	UK	Smooth Sliky Salty	44929	1421	285
13	Curtice Advani	USA	Almond Choco	44929	1617	56
14	Jehu Rudeforth	USA	Organic Choco Syrup	44929	3976	362
15	Barr Faughny	India	After Nines	44929	28	2
16	Oby Sorrel	UK	85% Dark Bars	44929	8785	489
17	Husein Augar	Australia	Smooth Sliky Salty	44929	18081	3014
18	Gunar ****shoot	USA	Milk Bars	44929	2422	143
19	Oby Sorrel	India	50% Dark Bites	44929	2618	437
20	Dotty Strutley	New Zealand	Eclairs	44929	2359	74
21	Gigi Bohling	Canada	99% Dark & Pure	44929	2380	85
22	Karlen McCaffrey	India	Milk Bars	44929	6391	427
23	Wilone O'Kielt	Australia	70% Dark Bites	44929	3668	127
24	Brien Boise	New Zealand	99% Dark & Pure	44929	13391	536
25	Karlen McCaffrey	USA	Choco Coated Almonds	44929	12691	977
26	Brien Boise	India	Almond Choco	44929	294	11
27	Karlen McCaffrey	UK	85% Dark Bars	44930	6069	380
28	Rafaelita Blaksland	Australia	50% Dark Bites	44930	2450	273
29	Van Tuxwell	USA	Fruit & Nut Bars	44930	2002	182
30	Brien Boise	New Zealand	Orange Choco	44930	1330	103
31	Ches Bonnell	USA	Almond Choco	44930	11179	466
32	Andria Kimpton	USA	Baker's Choco Chips	44930	2877	170
33	Oby Sorrel	USA	Orange Choco	44930	6510	434
34	Rafaelita Blaksland	India	85% Dark Bars	44930	1925	121
35	Wilone O'Kielt	UK	70% Dark Bites	44930	6874	265
36	Brien Boise	USA	Spicy Special Slims	44930	6965	465
37	Wilone O'Kielt	New Zealand	Fruit & Nut Bars	44930	3780	420
38	Gigi Bohling	New Zealand	Smooth Sliky Salty	44930	7322	611
39	Beverie Moffet	Canada	Choco Coated Almonds	44930	7168	359
40	Barr Faughny	UK	Caramel Stuffed Bars	44931	2114	97
41	Husein Augar	UK	Smooth Sliky Salty	44931	11144	1592
42	Mallorie Waber	Canada	85% Dark Bars	44931	3857	184
43	Gigi Bohling	USA	Milk Bars	44931	6545	385
44	Karlen McCaffrey	UK	After Nines	44931	728	32
45	Jan Morforth	New Zealand	70% Dark Bites	44932	3857	143
46	Mallorie Waber	UK	Almond Choco	44932	5425	217
47	Oby Sorrel	Canada	After Nines	44932	6776	283
48	Barr Faughny	USA	Organic Choco Syrup	44932	2961	198
49	Karlen McCaffrey	India	Smooth Sliky Salty	44932	2317	331
50	Marney O'Breen	New Zealand	After Nines	44932	13286	493
51	Roddy Speechley	New Zealand	Baker's Choco Chips	44932	8393	525
52	Roddy Speechley	India	Raspberry Choco	44932	6587	236
53	Madelene Upcott	Australia	Almond Choco	44932	147	6
54	Gigi Bohling	USA	99% Dark & Pure	44932	13391	536
55	Kelci Walkden	USA	Almond Choco	44932	2184	100
56	Oby Sorrel	UK	After Nines	44932	6951	366
57	Gunar ****shoot	India	99% Dark & Pure	44932	3927	152
58	Camilla Castle	Australia	50% Dark Bites	44935	5159	737
59	Marney O'Breen	New Zealand	Manuka Honey Choco	44935	8456	423
60	Barr Faughny	New Zealand	Manuka Honey Choco	44935	13454	612
61	Oby Sorrel	UK	Orange Choco	44935	7287	405
62	Mallorie Waber	India	Mint Chip Choco	44935	6069	243
63	Van Tuxwell	India	White Choc	44935	3878	243
64	Madelene Upcott	New Zealand	Baker's Choco Chips	44935	7812	558
65	Wilone O'Kielt	Canada	Raspberry Choco	44935	308	15
66	Wilone O'Kielt	Canada	Manuka Honey Choco	44936	6342	289
67	Madelene Upcott	India	Orange Choco	44936	3003	301
68	Gunar ****shoot	New Zealand	Drinking Coco	44936	924	37
69	Van Tuxwell	New Zealand	70% Dark Bites	44936	2345	79
70	Marney O'Breen	India	Peanut Butter Cubes	44936	5565	232
71	Ches Bonnell	USA	White Choc	44936	420	28
72	Gigi Bohling	USA	Raspberry Choco	44936	6888	265
73	Gunar ****shoot	Australia	Manuka Honey Choco	44936	7147	286
74	Kelci Walkden	India	Mint Chip Choco	44936	11424	369
75	Gunar ****shoot	Australia	Raspberry Choco	44936	2457	112
76	Dennison Crosswaite	New Zealand	Choco Coated Almonds	44936	5698	475
77	Dennison Crosswaite	India	Orange Choco	44936	6216	415
78	Gunar ****shoot	UK	Almond Choco	44936	2723	91
79	Dotty Strutley	New Zealand	Smooth Sliky Salty	44936	7532	754
80	Dotty Strutley	India	After Nines	44936	8729	437
81	Van Tuxwell	Canada	85% Dark Bars	44936	16968	808
82	Curtice Advani	New Zealand	Raspberry Choco	44936	7679	334
83	Camilla Castle	USA	85% Dark Bars	44936	7091	473
84	Jehu Rudeforth	New Zealand	50% Dark Bites	44936	1897	146
85	Curtice Advani	New Zealand	Spicy Special Slims	44936	7924	721
86	Marney O'Breen	Canada	Mint Chip Choco	44936	10241	366
87	Gigi Bohling	Australia	After Nines	44936	3045	133
88	Oby Sorrel	USA	Organic Choco Syrup	44936	13692	1141
89	Gunar ****shoot	India	After Nines	44936	6405	292
90	Oby Sorrel	India	Smooth Sliky Salty	44936	4389	732
91	Camilla Castle	New Zealand	Smooth Sliky Salty	44936	476	40
92	Gunar ****shoot	UK	Choco Coated Almonds	44936	168	12
93	Madelene Upcott	Canada	Mint Chip Choco	44936	15204	585
94	Gunar ****shoot	UK	Mint Chip Choco	44936	4781	184
95	Husein Augar	Australia	Raspberry Choco	44937	2429	90
96	Madelene Upcott	UK	Manuka Honey Choco	44937	6223	260
97	Camilla Castle	New Zealand	Drinking Coco	44937	16037	802
98	Rafaelita Blaksland	USA	Orange Choco	44937	6587	388
99	Husein Augar	New Zealand	Mint Chip Choco	44937	4697	168
100	Husein Augar	India	Almond Choco	44937	9457	430
Sheet1

Cell Formulas
Range	Formula
A1:F2752	A1	=REF3D(VSTACK('[monthly-workbook.xlsx]Jan:End'!$A$1:$G$1000),1)
H4	H4	=FORMULATEXT(H5)
H5	H5	=ROWS(A1#)-1
Dynamic array formulas.

Calling some APIVOT is a breeze, everything fully dynamic, no refresh needed, we can add more spreadsheets before "End" one, ready in 2s

REF3D.xlsx
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2		Product	1-Jan	2-Feb	3-Mar	4-Apr	5-May	6-Jun	GT		S.Person	Australia	Canada	India	New Zealand	UK	USA	Amount
3		50% Dark Bites	85113	94808	78680	101234	158480	149807	668122		Andria Kimpton	132314	96495	119063	105259	105924	91861	650916
4		70% Dark Bites	108941	108192	126147	101304	170128	139881	754593		Barr Faughny	100961	112700	113897	116102	89635	93786	627081
5		85% Dark Bars	146993	88466	78463	134729	89663	147707	686021		Beverie Moffet	103901	112812	99988	110383	140364	63721	631169
6		99% Dark & Pure	126539	79233	77154	143395	156422	111321	694064		Brien Boise	86058	134946	95025	133301	90951	98294	638575
7		After Nines	172970	76748	92764	59206	136353	137599	675640		Camilla Castle	121947	97349	102018	92456	92841	85729	592340
8		Almond Choco	84798	114429	104958	69776	132874	63329	570164		Ches Bonnell	87283	93436	70840	71197	104825	124264	551845
9		Baker's Choco Chips	67172	79590	168413	62160	198149	156996	732480		Curtice Advani	101920	108675	97790	119357	77525	73381	578648
10		Caramel Stuffed Bars	68250	112210	134484	93737	132601	150759	692041		Dennison Crosswaite	65709	102165	84049	117516	94227	158165	621831
11		Choco Coated Almonds	97790	115500	61516	107877	150451	120736	653870		Dotty Strutley	97594	103530	110327	108283	136955	79821	636510
12		Drinking Coco	107065	108360	96369	148176	102095	147952	710017		Gigi Bohling	85806	101255	67403	102781	78197	106869	542311
13		Eclairs	100632	101073	121905	84623	178255	138061	724549		Gunar ****shoot	92288	85540	115178	112189	84924	125748	615867
14		Fruit & Nut Bars	105259	103236	59731	133553	111440	97447	610666		Husein Augar	102186	124152	97783	104265	96789	97566	622741
15		Manuka Honey Choco	128779	94878	54327	165564	187509	113358	744415		Jan Morforth	121079	78995	81333	102697	132412	94640	611156
16		Milk Bars	86436	88284	98119	141610	151459	110019	675927		Jehu Rudeforth	102585	79373	122801	107226	91280	98623	601888
17		Mint Chip Choco	144655	100828	117663	101696	140609	75614	681065		Kaine Padly	57288	129871	112252	78239	106197	97069	580916
18		Orange Choco	88368	86975	87115	61187	120456	165018	609119		Karlen McCaffrey	141099	116991	120246	101227	76797	103089	659449
19		Organic Choco Syrup	101668	108829	87234	98805	233051	114303	743890		Kelci Walkden	96397	102179	122612	113365	118881	97013	650447
20		Peanut Butter Cubes	80374	86177	99666	139937	112917	117138	636209		Madelene Upcott	69706	116956	95620	98672	112168	91056	584178
21		Raspberry Choco	147322	148561	106365	76160	114695	172732	765835		Mallorie Waber	80598	109039	105770	72016	94892	71141	533456
22		Smooth Sliky Salty	125209	109214	93597	62643	166348	126700	683711		Marney O'Breen	110992	98826	107905	123144	113197	81564	635628
23		Spicy Special Slims	113561	126203	56147	107457	126308	164150	693826		Oby Sorrel	138593	126847	102130	87080	107849	97685	660184
24		White Choc	53382	122878	121114	92022	148190	205170	742756		Rafaelita Blaksland	74270	123389	80962	122983	96803	106974	605381
25		GT	2341276	2254672	2121931	2286851	3218453	2925797	15148980		Roddy Speechley	128058	101843	104398	96845	97020	88249	616413
26											Van Tuxwell	125692	77630	46998	119196	75726	94381	539623
27											Wilone O'Kielt	106001	69755	85470	101626	95970	101605	560427
28											GT	2530325	2604749	2461858	2617405	2512349	2422294	15148980
29
30
31		S.Person	Amount								S.Person	Boxes
32		Andria Kimpton	650916								Andria Kimpton	38833
33		Barr Faughny	627081								Barr Faughny	35853
34		Beverie Moffet	631169								Beverie Moffet	38247
35		Brien Boise	638575								Brien Boise	38329
36		Camilla Castle	592340								Camilla Castle	35891
37		Ches Bonnell	551845								Ches Bonnell	34906
38		Curtice Advani	578648								Curtice Advani	38074
39		Dennison Crosswaite	621831								Dennison Crosswaite	38168
40		Dotty Strutley	636510								Dotty Strutley	39785
41		Gigi Bohling	542311								Gigi Bohling	33003
42		Gunar ****shoot	615867								Gunar ****shoot	37195
43		Husein Augar	622741								Husein Augar	38806
44		Jan Morforth	611156								Jan Morforth	37778
45		Jehu Rudeforth	601888								Jehu Rudeforth	35355
46		Kaine Padly	580916								Kaine Padly	34290
47		Karlen McCaffrey	659449								Karlen McCaffrey	41216
48		Kelci Walkden	650447								Kelci Walkden	38757
49		Madelene Upcott	584178								Madelene Upcott	35852
50		Mallorie Waber	533456								Mallorie Waber	31962
51		Marney O'Breen	635628								Marney O'Breen	38352
52		Oby Sorrel	660184								Oby Sorrel	43683
53		Rafaelita Blaksland	605381								Rafaelita Blaksland	34216
54		Roddy Speechley	616413								Roddy Speechley	39477
55		Van Tuxwell	539623								Van Tuxwell	33846
56		Wilone O'Kielt	560427								Wilone O'Kielt	31582
57		GT	15148980								GT	923456
58
Sheet1

Cell Formulas
Range	Formula
J2:Q25	J2	=LET(a,DROP(A1#,1),APIVOT(INDEX(a,,3),TEXT(INDEX(a,,4),"m-mmm"),INDEX(a,,5),LAMBDA(x,SUM(x)),"Product"))
S2:Z28	S2	=LET(a,DROP(A1#,1),APIVOT(INDEX(a,,1),TEXT(INDEX(a,,2),"m-mmm"),INDEX(a,,5),LAMBDA(x,SUM(x)),"S.Person","Amount"))
J31:K57	J31	=LET(a,DROP(A1#,1),APIVOT(INDEX(a,,1),,INDEX(a,,5),LAMBDA(x,SUM(x)),"S.Person","Amount"))
S31:T57	S31	=LET(a,DROP(A1#,1),APIVOT(INDEX(a,,1),,INDEX(a,,6),LAMBDA(x,SUM(x)),"S.Person","Boxes"))
Dynamic array formulas.

 

[Xlambda]

A function that a had fun before, it's what I like to call an interactive function on multiple levels. We can "print" a full list of options, we can select based on those options, filtered outcome based on selected options
SLICER(ar,cl,[lst],[sl])
ar: array
cl: column index of the slicer
[lst]: list argument: can be omitted or can be a reference of the selected slicers
[sl]: select argument, can be omitted or as text string of indexes separated by comas (like in"1,3,5")
interactive functionality:
- if lst is omitted and sl is omitted will have the full list/array of slicer values and their index.
- if lst is omitted and sl is for example "1,3,5", the function will return only those chosen slicers that correspond with respective indexes
- if lst is not omitted, and refers to the refrence of the array of slicer values, the function will return the main array filtered based on the slicer values

=LAMBDA(ar, cl, [lst], [sl],
    LET(
        a, IF(ar = "", "", ar),
        c, INDEX(a, , cl),
        s, SORT(UNIQUE(c)),
        IF(
            ISOMITTED(lst),
            IF(ISOMITTED(sl), HSTACK(SEQUENCE(ROWS(s)), s), INDEX(s, --TEXTSPLIT(sl, , ","))),
            FILTER(a, REDUCE(0, lst, LAMBDA(v, i, v + (c = i))))
        )
    )
)

REF3D.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	shorter sample of main big array							1st interactive use			2nd interactive use
2	Sales Person	Country	Product	Date	Amount	Boxes		lst,sl,omitted			lst,omitted,sl,"1,3,5"
3	Ches Bonnell	Australia	Choco Coated Almonds	44928	12971	721		all slicer values			based on visualizing all slicers, we choose what we need
4	Roddy Speechley	UK	70% Dark Bites	44928	1610	68		=SLICER(A3:F71,2)			=SLICER(A3:F71,2,,"1,3,5")
5	Rafaelita Blaksland	New Zealand	70% Dark Bites	44928	518	18		1	Australia		Australia
6	Roddy Speechley	New Zealand	Mint Chip Choco	44928	1393	45		2	Canada		India
7	Wilone O'Kielt	New Zealand	Raspberry Choco	44928	1064	51		3	India		UK
8	Ches Bonnell	USA	Peanut Butter Cubes	44929	1988	111		4	New Zealand
9	Oby Sorrel	India	Choco Coated Almonds	44929	2457	145		5	UK
10	Camilla Castle	Australia	Mint Chip Choco	44929	11158	360		6	USA		lst, now is a refrence obtained by other interactive instance of same function
11	Marney O'Breen	UK	Fruit & Nut Bars	44929	2961	198					3rd interactive use, array filtered based on slicer values
12	Mallorie Waber	UK	Spicy Special Slims	44929	3738	340					=SLICER(A3:F71,2,K5#)
13	Beverie Moffet	UK	Smooth Sliky Salty	44929	1421	285					Ches Bonnell	Australia	Choco Coated Almonds	02-01-23	12971	721
14	Curtice Advani	USA	Almond Choco	44929	1617	56					Roddy Speechley	UK	70% Dark Bites	02-01-23	1610	68
15	Jehu Rudeforth	USA	Organic Choco Syrup	44929	3976	362					Oby Sorrel	India	Choco Coated Almonds	03-01-23	2457	145
16	Barr Faughny	India	After Nines	44929	28	2					Camilla Castle	Australia	Mint Chip Choco	03-01-23	11158	360
17	Oby Sorrel	UK	85% Dark Bars	44929	8785	489					Marney O'Breen	UK	Fruit & Nut Bars	03-01-23	2961	198
18	Husein Augar	Australia	Smooth Sliky Salty	44929	18081	3014					Mallorie Waber	UK	Spicy Special Slims	03-01-23	3738	340
19	Gunar ****shoot	USA	Milk Bars	44929	2422	143					Beverie Moffet	UK	Smooth Sliky Salty	03-01-23	1421	285
20	Oby Sorrel	India	50% Dark Bites	44929	2618	437					Barr Faughny	India	After Nines	03-01-23	28	2
21	Dotty Strutley	New Zealand	Eclairs	44929	2359	74					Oby Sorrel	UK	85% Dark Bars	03-01-23	8785	489
22	Gigi Bohling	Canada	99% Dark & Pure	44929	2380	85					Husein Augar	Australia	Smooth Sliky Salty	03-01-23	18081	3014
23	Karlen McCaffrey	India	Milk Bars	44929	6391	427					Oby Sorrel	India	50% Dark Bites	03-01-23	2618	437
24	Wilone O'Kielt	Australia	70% Dark Bites	44929	3668	127					Karlen McCaffrey	India	Milk Bars	03-01-23	6391	427
25	Brien Boise	New Zealand	99% Dark & Pure	44929	13391	536					Wilone O'Kielt	Australia	70% Dark Bites	03-01-23	3668	127
26	Karlen McCaffrey	USA	Choco Coated Almonds	44929	12691	977					Brien Boise	India	Almond Choco	03-01-23	294	11
27	Brien Boise	India	Almond Choco	44929	294	11					Karlen McCaffrey	UK	85% Dark Bars	04-01-23	6069	380
28	Karlen McCaffrey	UK	85% Dark Bars	44930	6069	380					Rafaelita Blaksland	Australia	50% Dark Bites	04-01-23	2450	273
29	Rafaelita Blaksland	Australia	50% Dark Bites	44930	2450	273					Rafaelita Blaksland	India	85% Dark Bars	04-01-23	1925	121
30	Van Tuxwell	USA	Fruit & Nut Bars	44930	2002	182					Wilone O'Kielt	UK	70% Dark Bites	04-01-23	6874	265
31	Brien Boise	New Zealand	Orange Choco	44930	1330	103					Barr Faughny	UK	Caramel Stuffed Bars	05-01-23	2114	97
32	Ches Bonnell	USA	Almond Choco	44930	11179	466					Husein Augar	UK	Smooth Sliky Salty	05-01-23	11144	1592
33	Andria Kimpton	USA	Baker's Choco Chips	44930	2877	170					Karlen McCaffrey	UK	After Nines	05-01-23	728	32
34	Oby Sorrel	USA	Orange Choco	44930	6510	434					Mallorie Waber	UK	Almond Choco	06-01-23	5425	217
35	Rafaelita Blaksland	India	85% Dark Bars	44930	1925	121					Karlen McCaffrey	India	Smooth Sliky Salty	06-01-23	2317	331
36	Wilone O'Kielt	UK	70% Dark Bites	44930	6874	265					Roddy Speechley	India	Raspberry Choco	06-01-23	6587	236
37	Brien Boise	USA	Spicy Special Slims	44930	6965	465					Madelene Upcott	Australia	Almond Choco	06-01-23	147	6
38	Wilone O'Kielt	New Zealand	Fruit & Nut Bars	44930	3780	420					Oby Sorrel	UK	After Nines	06-01-23	6951	366
39	Gigi Bohling	New Zealand	Smooth Sliky Salty	44930	7322	611					Gunar ****shoot	India	99% Dark & Pure	06-01-23	3927	152
40	Beverie Moffet	Canada	Choco Coated Almonds	44930	7168	359					Camilla Castle	Australia	50% Dark Bites	09-01-23	5159	737
41	Barr Faughny	UK	Caramel Stuffed Bars	44931	2114	97					Oby Sorrel	UK	Orange Choco	09-01-23	7287	405
42	Husein Augar	UK	Smooth Sliky Salty	44931	11144	1592					Mallorie Waber	India	Mint Chip Choco	09-01-23	6069	243
43	Mallorie Waber	Canada	85% Dark Bars	44931	3857	184					Van Tuxwell	India	White Choc	09-01-23	3878	243
44	Gigi Bohling	USA	Milk Bars	44931	6545	385					Madelene Upcott	India	Orange Choco	10-01-23	3003	301
45	Karlen McCaffrey	UK	After Nines	44931	728	32					Marney O'Breen	India	Peanut Butter Cubes	10-01-23	5565	232
46	Jan Morforth	New Zealand	70% Dark Bites	44932	3857	143
47	Mallorie Waber	UK	Almond Choco	44932	5425	217
48	Oby Sorrel	Canada	After Nines	44932	6776	283
49	Barr Faughny	USA	Organic Choco Syrup	44932	2961	198
50	Karlen McCaffrey	India	Smooth Sliky Salty	44932	2317	331
51	Marney O'Breen	New Zealand	After Nines	44932	13286	493
52	Roddy Speechley	New Zealand	Baker's Choco Chips	44932	8393	525
53	Roddy Speechley	India	Raspberry Choco	44932	6587	236
54	Madelene Upcott	Australia	Almond Choco	44932	147	6
55	Gigi Bohling	USA	99% Dark & Pure	44932	13391	536
56	Kelci Walkden	USA	Almond Choco	44932	2184	100
57	Oby Sorrel	UK	After Nines	44932	6951	366
58	Gunar ****shoot	India	99% Dark & Pure	44932	3927	152
59	Camilla Castle	Australia	50% Dark Bites	44935	5159	737
60	Marney O'Breen	New Zealand	Manuka Honey Choco	44935	8456	423
61	Barr Faughny	New Zealand	Manuka Honey Choco	44935	13454	612
62	Oby Sorrel	UK	Orange Choco	44935	7287	405
63	Mallorie Waber	India	Mint Chip Choco	44935	6069	243
64	Van Tuxwell	India	White Choc	44935	3878	243
65	Madelene Upcott	New Zealand	Baker's Choco Chips	44935	7812	558
66	Wilone O'Kielt	Canada	Raspberry Choco	44935	308	15
67	Wilone O'Kielt	Canada	Manuka Honey Choco	44936	6342	289
68	Madelene Upcott	India	Orange Choco	44936	3003	301
69	Gunar ****shoot	New Zealand	Drinking Coco	44936	924	37
70	Van Tuxwell	New Zealand	70% Dark Bites	44936	2345	79
71	Marney O'Breen	India	Peanut Butter Cubes	44936	5565	232
72
Sheet2

Cell Formulas
Range	Formula
H4,K12,K4	H4	=FORMULATEXT(H5)
H5:I10	H5	=SLICER(A3:F71,2)
K5:K7	K5	=SLICER(A3:F71,2,,"1,3,5")
K13:P45	K13	=SLICER(A3:F71,2,K5#)
Dynamic array formulas.

Using SLICER with APIVOT on the big data set, full dynamic and interactive.
One interactive use of SLICER , for selecting certain values out of all possibilities. We keep it as refrence for visualizing purposes.
Other interactive use of SLICER inside APIVOT as filtered by selection array

REF3D.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		=SLICER(DROP(Sheet1!A1#,1),2,,"1,3,5")
3		↓↓		=LET(a,SLICER(DROP(Sheet1!A1#,1),2,B5#),APIVOT(INDEX(a,,1),INDEX(a,,2),INDEX(a,,5),LAMBDA(x,SUM(x)),"S.Person","Amount"))
4		↓↓		↓↓
5		Australia		S.Person	Australia	India	UK	Amount
6		India		Andria Kimpton	132314	119063	105924	357301
7		UK		Barr Faughny	100961	113897	89635	304493
8				Beverie Moffet	103901	99988	140364	344253
9				Brien Boise	86058	95025	90951	272034
10				Camilla Castle	121947	102018	92841	316806
11				Ches Bonnell	87283	70840	104825	262948
12				Curtice Advani	101920	97790	77525	277235
13				Dennison Crosswaite	65709	84049	94227	243985
14				Dotty Strutley	97594	110327	136955	344876
15				Gigi Bohling	85806	67403	78197	231406
16				Gunar ****shoot	92288	115178	84924	292390
17				Husein Augar	102186	97783	96789	296758
18				Jan Morforth	121079	81333	132412	334824
19				Jehu Rudeforth	102585	122801	91280	316666
20				Kaine Padly	57288	112252	106197	275737
21				Karlen McCaffrey	141099	120246	76797	338142
22				Kelci Walkden	96397	122612	118881	337890
23				Madelene Upcott	69706	95620	112168	277494
24				Mallorie Waber	80598	105770	94892	281260
25				Marney O'Breen	110992	107905	113197	332094
26				Oby Sorrel	138593	102130	107849	348572
27				Rafaelita Blaksland	74270	80962	96803	252035
28				Roddy Speechley	128058	104398	97020	329476
29				Van Tuxwell	125692	46998	75726	248416
30				Wilone O'Kielt	106001	85470	95970	287441
31				GT	2530325	2461858	2512349	7504532
32
Sheet3

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(B5)
D3	D3	=FORMULATEXT(D5)
B5:B7	B5	=SLICER(DROP(Sheet1!A1#,1),2,,"1,3,5")
D5:H31	D5	=LET(a,SLICER(DROP(Sheet1!A1#,1),2,B5#),APIVOT(INDEX(a,,1),INDEX(a,,2),INDEX(a,,5),LAMBDA(x,SUM(x)),"S.Person","Amount"))
Dynamic array formulas.

 

[Xlambda]

Another quick one, cool latest video of @MrExcel, 1h ago

APIVOT can take array calculations (virtual/helper columns that are not even there)
Data sample as is the downloadable workbook

Excel_Calculate_Average_Repair_Costs_By_Age_Of_Vehicle_Episode_2627.xlsx
	A	B	C	D	E	F	G
1	Vehicle	Service Date	Service Description	Costs
2	V-001	02-11-2004	JQBZ AZB	132.75
3	V-001	07-22-2004	JQBZ AZB	0.00		rows of data
4	V-001	01-03-2005	BJAQ JABB QJQJJ QA BAJQQABQAQ	129.28		=ROWS(A1:D4468)-1
5	V-001	01-31-2005	JQBZ AZB	273.09		4467
6	V-001	01-31-2005	BJAQ JABB QJQJJ AJ BAQQAQZ BABJA	135.45
7	V-001	05-12-2005	BJAQ JABB QJQJJ QQAQQAQ JABJAB AJ	211.41
8	V-001	06-15-2005	BJAQ JABB QJQJJ QA/AJ QAQAQ/BJAZAQ JAQAQ	1,173.53
9	V-001	08-18-2005	JQBZ AZB	378.34
10	V-001	02-24-2006	JQBZ AZB	40.17
11	T-004	07-24-2006	AJABQQABAJ	0.00
12	V-001	08-09-2006	JQBZ AZB	311.65
13	T-004	08-31-2006	AJAB JABQ QAAJ JABBQ AQQZ BQAJAJ ABB	604.89
14	V-001	10-18-2006	QAQAQ BJAQ	217.56
15	T-005	10-19-2006	JQAZAJQAQAZA JAAJQAJAJBA [2000-2199]	369.72
16	T-005	10-19-2006	AQAAJ QAQAAQA QAJJZ JABQ BAQAQ	164.84
17	V-001	02-05-2007	QAQA QBQAA BAZA QJAZ JAAJ/JAAQAJB AAQ	215.38
18	V-001	02-12-2007	BA/QAQJ JAJJAJB AJB QABAQ QAABB	1,068.60
19	V-001	03-07-2007	JQBZ AZB	280.18
20	T-005	03-29-2007	J/B BAAB JAQBB AJAJAQAQAZA	86.32
21	T-004	04-11-2007	JQAZAJQAQAZA JAAJQAJAJBA	281.25
22	T-005	04-26-2007	JQAZAJQAQAZA JAAJQAJAJBA	353.01
23	V-001	05-21-2007	BBQBQZQ BABJAQ & QAQJAJAJQ BAQQABAB	171.52
24	V-001	07-19-2007	QAQA QAJJABA QJAQA	391.84
25	T-004	09-28-2007	JJ AJQJABQAAJ [A,B JJ]	284.76
26	V-001	10-04-2007	JQBZ AZB [ABB JJ]	805.93
27	V-001	10-04-2007	AZBAAQQ BAJQ	97.85
28	T-005	10-10-2007	JJ AJQJABQAAJ [A,B,B JJ]	711.41
29	T-004	10-19-2007	JABBQ QAQJ QABJAJ JAQ ZAQJAJB	201.57
30	T-006	01-22-2008	JJ AJQJABQAAJ [A JJ] 2JB QAJA	199.20
31	T-013	03-07-2008	JJ AJQJABQAAJ [A JJ]	344.37
32	T-012	03-19-2008	JJ AJQJABQAAJ [A JJ]	187.05
33	T-004	03-19-2008	JJ AJQJABQAAJ [A.B. JJ]	351.93
Data

Cell Formulas
Range	Formula
F4	F4	=FORMULATEXT(F5)
F5	F5	=ROWS(A1:D4468)-1

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Data!$A$1:$D$1	F5
DATA4	=Data!$C$2:$C$4468	F5
DATA5	=Data!$D$2:$D$4468	F5
TEST1	=Data!$A$3335:$D$4468	F5
TESTHKEY	=Data!$C$1:$D$1	F5

Single cell formula for SUMs

=LET(v,A2:A4468,d,B2:B4468,s,DATA5,APIVOT(v,DATEDIF(MAP(v,LAMBDA(x,MIN(IF(x=v,d))))-90,d,"Y")+1,s,LAMBDA(x,SUM(x))))

Excel_Calculate_Average_Repair_Costs_By_Age_Of_Vehicle_Episode_2627.xlsx
	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
2		PT	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	GT
3		T-004	886.14	838.26	684.04	1565.77	383.9	1004.01	58.3	1243.74	5253.58	537.7	1163.12	130.48	1999.11	2682.04	1249.46	963.46	2675.83	2814.45			26133.39
4		T-005	973.89	976.53	1035.99	1036.54	1433.34	627.27	467.6	1013	914.13	472.24	238.67	797.82	816.37	1640.24	3328.88	1065.79	1008.63	0			17846.93
5		T-006	1114.08	1028.45	635.77	267.11	463.87	311	497.11	322.83	454.72	1378.75	154.31	491.46	1007.58	1020.81	482.95	121.36					9752.16
6		T-010	1420.65	1154.96	486.79	1466.53	3560.09	3829.21	1196.41	882.44	2845.63	1712.06	1738.47	648.5	1374.64	730.9	1746.27	5300.26					30093.81
7		T-011	430.62	1060.15	808.13	2965.14	1059.39	4007.74	1150.75	1625.15	1488.05	378.06	705.45	5642.37	5841.47	3774.72	2481.14	701.89					34120.22
8		T-012	1082.09	939.6	884.88	3595.55	961.72	989.41	496.01	1541.74	1385.24	1341.27	2585.54	773.34	2051.28	8796.89	5449.14	857.22					33730.92
9		T-013	1738.5	1061.68	2111.14	2775.4	3873.5	568.35	1040.47	2929.39	1349.06	1332.49	1259.08	2243.05	1650.24	4624.79	2802.42	5326.97					36686.53
10		T-017	1275.93	1292.94	1520.96	1727.93	2828.22	3091.75	4314.83	4980.77	3342.29	3359.8	12052.25	8043.29	2025.79								49856.75
11		T-018	549.64	1193.82	623.65	432.22	2042.29	1212.15	579.87	1056.82	1535.97	589.92	368.13	984.56	1938.62	700	0						13807.66
12		T-020	1461.86	1463.54	1530.8	2442.92	1191.06	7057.97	10677.64	2547.15	8375.46	4349.29	7270.19	8903.05	10409.99	8541.4	2032.44						78254.76
13		T-022	732.96	1456.03	1292.13	1837.21	2097.9	1139.18	19691.19	2061.83	7675.98	9711.49	2923.63	3283.82	10685.13	2527.75	4072.56						71188.79
14		T-024	685.81	1555.46	1364.42	3042.08	1880.56	1221.82	2053.62	1727.89	2838.71	2106.58	1399.54	5732.49	1175.36	443.92	3237.91						30466.17
15		T-026	2096.01	1822.75	3423.79	3877.06	4081.11	402.05	5397.74	4037.48	3189.13	8408.63	5343.32	2786.85	3325.11	16702	213.65						65106.68
16		T-029	812.39	1178.51	1237.09	290.77	763.95	1561.73	905.45	658.25	535.9	983.98	781.86	3142.11	3464.79	727.04							17043.82
17		T-035	463.63	5331.02	100.02	493.88	365.71	1047.22	987.57	631.27	1660.92	855.29	250.25	636.29	832.7	2937.14							16592.91
18		T-037	625.34	2979.17	763.88	194.03	981.82	1721.47	950.16	2410.32	1978.26	6868.94	6806.51	3258.66	2681.57								32220.13
19		T-044	1238.89	922.68	949.67	902.39	1657.89	9388.91	5581.26	9362.63	2766.79	3452.41	2890.24										39113.76
20		T-045	549.36	4142.51	628.35	4081.64	1003.21	2893.65	1751.79	2120.22	5934.16	3663.97	3088.86	132.73									29990.45
21		T-049	1219.33	1123.8	3051.97	4128.41	1495.33	6223.69	6492.2	4332.89	7754.75	5594.84	12713.99										54131.2
22		T-050	279.61	2802.46	1013.74	5361.3	1683.29	10115.61	14699.06	4691.11	6729.88	8436.62	4378.61	5.51									60196.8
23		T-051	0	427.41	2158.14	1863.02	8614.72	3219.76	9707.21	2876.84	5052.48	7396.23	454.52										41770.33
24		T-052	389.03	2696.91	2889.22	5660.58	4135.93	4244.95	6574.77	9856.03	2469.25												38916.67
25		T-056	1733.6	534.96	1237.4	651.97	2730.75	1888.22	2457.28	4815.49	0												16049.67
26		T-058	1627.88	283.73	449.38	688.45	1008.18	4615.43	735.2	0													9408.25
27		T-060	925.02	614.43	824.14	1268.98	7804.86	2093.63	1072.15	29.09													14632.3
28		T-061	159.59	341	470.28	377.84	452.51	636.72	273.57														2711.51
29		T-063	262.61	4635.86	1995.53	4716.72	6438.8	4930.08	15333.16														38312.76
30		T-069	404.31	1224.01	1423.87	1493.64	2539.22																7085.05
31		T-070	494.31	697.69	1088.88	1138.01	632.05																4050.94
32		T-071	603.69	810.24	1443.42	1013.48	1196.17																5067
33		V-001	132.75	2301.1	569.38	3031.3	601.32	3593.24	702.52	507	2374.2	907.01	2090.39	3975.97	917.7	836.19	1383.11	734.11	640.83	2912.57	3966.54	3652.84	35830.07
34		V-008	662.8	927.36	1488.24	984.74	2582.66	2812.07	3116.29	1600.26	4079.77	3108.81	1577.53	2736.72	795.21	2555.14	1077.29	2207.72					32312.61
35		V-009	1625.23	972.09	597.31	950.36	2590.15	7292.67	355.01	1020.39	1670.48	829.25	577.62	1248.89	761.34	937.37	2106.48	965.02					24499.66
36		V-019	685.7	208.92	0	337.61	94.46	2170.12	2249.05	382.42	1503.12	3500.23	2946.4	376.02	2523.59	1246.64	4106.1						22330.38
37		V-023	704.16	3088.07	421.21	624.55	2583.03	2388.32	1461.05	90.03	2646.72	1843.62	12771.21	9113.17	2201.34	7649.74	1287.58						48873.8
38		V-028	443.13	1141.24	2779.05	215.87	12144.54	3398.83	1210.22	487.86	3947.8	688.72	1625.58	806.65	1905.71								30795.2
39		V-032	626.16	1671.26	850.07	1359.64	202.44	2105.37	454.98	377.73	1673.13	1659.28	1982.81	1752.12	3079.06	1437.07							19231.12
40		V-034	580.35	631.93	313.7	709.35	344.26	777.57	804.86	683.23	1024.91	2784.17	323.64	868.51	792.93	1775.76							12415.17
41		V-036	509.91	2221.72	1206.16	2657.54	4828.16	5233.26	5166.6	8985.03	6627.26	5584.81	4913.49	176.26									48110.2
42		V-039	613.51	928.52	910.36	1698.91	3789.73	1941.52	2324.11	9004.27	1288.13	1339.25	1865.66	3177.5	5370.8								34252.27
43		V-040	838.97	589.17	1376.01	1822.34	747.62	3783.65	383.91	624.61	6024.24	6030.06	4036.25	6792.86	1263.88								34313.57
44		V-042	1141.64	1463.93	1574.77	2293.51	3779.4	5636.8	1343.71	3131.56	949.14	1355.5	466.51										23136.47
45		V-043	3357.23	2560.94	1789.22	4557.03	2784.72	5906.4	887.11	5064.6	16486.66	4334.41	0										47728.32
46		V-046	1660.37	1160.65	2929.23	2150.32	2728.75	4057.87	8159.53	1344.75	1275.47	1978.15	1779.6										29224.69
47		V-047	682.72	869	1433.95	603.93	946.65	3727.07	2480.17	738.01	8383.02	1181.06	393.26										21438.84
48		V-048	813.21	1041.94	1158	466.8	1927.83	790.61	892.36	2822.5	4539.2	798.3	1174.26										16425.01
49		V-053	324.49	360.38	1534.92	1817.93	1404.42	4125.73	3910.68	15349.43	3216.89	3163.71											35208.58
50		V-054	628.17	2108.76	2212.4	1238.82	1920.11	2636.34	3803.24	1976.92	3697.55	2881.29											23103.6
51		V-055	797.54	768.09	3639.93	983.16	2099.79	2255.53	5588.36	2274.99	4044.27	6489.4											28941.06
52		V-057	131.6	9257.15	5112.19	891.15	1376.05	1059.62	2553.75														20381.51
53		V-059	337.53	899.71	741.37	528.08	903.84	1872.58	5686.8														10969.91
54		V-062	653.34	4895.49	4395.99	1953.72	12017.28	13197.96	2445.95	0													39559.73
55		V-064	218.03	1152.8	1304.55	1120.61	4665.59	2034.79	4142.54														14638.91
56		V-065	829.51	848.17	738.91	3311.9	3199.08	3257.38	2459.14														14644.09
57		V-066	384.99	3186.37	2621.25	2435.08	3059.81	3157.4	3663.79														18508.69
58		V-067	1486.03	581.03	1715.86	511.47	5597.5																9891.89
59		V-072	401.05	398.58	842.49	1298.46	165.8																3106.38
60		GT	47506.89	92824.93	82383.99	101910.75	148446.33	169255.68	181388.1	124219.96	150982.3	123387.59	107090.75	78661.05	70891.31	72287.55	37057.38	18243.8	4325.29	5727.02	3966.54	3652.84	1624210.05
61
Data

Cell Formulas
Range	Formula
I2:AD60	I2	=LET(v,A2:A4468,d,B2:B4468,s,DATA5,APIVOT(v,DATEDIF(MAP(v,LAMBDA(x,MIN(IF(x=v,d))))-90,d,"Y")+1,s,LAMBDA(x,SUM(x))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DATA5	=Data!$D$2:$D$4468	I2
TEST1	=Data!$A$3335:$D$4468	I2

or for AVERAGEs

=LET(v,A2:A4468,d,B2:B4468,s,DATA5,APIVOT(v,DATEDIF(MAP(v,LAMBDA(x,MIN(IF(x=v,d))))-90,d,"Y")+1,s,LAMBDA(x,AVERAGE(x))))

Excel_Calculate_Average_Repair_Costs_By_Age_Of_Vehicle_Episode_2627.xlsx
	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
2		PT	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	GT
3		T-004	295.38	279.42	136.808	313.154	76.78	251.0025	58.3	177.67714	1313.395	134.425	145.39	65.24	333.185	536.408	249.892	321.153	382.261	562.89			318.699878
4		T-005	243.4725	488.265	147.9986	345.51333	286.668	156.8175	233.8	126.625	152.355	118.06	119.335	132.97	272.1233	410.06	832.22	266.448	252.158	0			241.17473
5		T-006	278.52	257.1125	211.9233	133.555	115.9675	103.66667	165.7033	107.61	113.68	344.6875	77.155	122.865	335.86	204.162	160.9833	60.68					184.003019
6		T-010	202.95	192.4933	121.6975	293.306	593.34833	319.10083	170.9157	176.488	316.1811	214.0075	347.694	162.125	458.2133	182.725	249.4671	1060.05					310.245464
7		T-011	215.31	176.6917	161.626	370.6425	211.878	364.34	164.3929	325.03	212.5786	42.006667	141.09	470.1975	730.1838	629.12	413.5233	701.89					331.264272
8		T-012	180.3483	156.6	147.48	719.11	137.38857	197.882	70.85857	256.95667	197.8914	191.61	287.28222	128.89	227.92	977.4322	389.2243	171.444					295.885263
9		T-013	217.3125	212.336	234.5711	462.56667	484.1875	142.0875	148.6386	418.48429	168.6325	111.04083	179.86857	320.4357	275.04	660.6843	254.7655	887.828					310.902797
10		T-017	159.4913	215.49	190.12	191.99222	235.685	257.64583	308.2021	415.06417	477.47	373.31111	927.09615	670.2742	337.6317								389.505859
11		T-018	274.82	170.5457	103.9417	144.07333	255.28625	173.16429	82.83857	352.27333	307.194	196.64	184.065	196.912	323.1033	175	0						200.111014
12		T-020	292.372	365.885	191.35	244.292	119.106	705.797	762.6886	363.87857	837.546	543.66125	727.019	1112.881	867.4992	2847.133	406.488						631.086774
13		T-022	244.32	145.603	258.426	229.65125	190.71818	162.74	1312.746	257.72875	548.2843	809.29083	265.78455	410.4775	971.3755	210.6458	581.7943						501.329507
14		T-024	137.162	194.4325	194.9171	338.00889	268.65143	174.54571	256.7025	143.99083	218.3623	351.09667	279.908	1433.123	391.7867	110.98	359.7678						284.730561
15		T-026	161.2315	202.5278	263.3685	352.46	510.13875	67.008333	674.7175	448.60889	318.913	1401.4383	593.70222	278.685	415.6388	2087.75	71.21667						496.997557
16		T-029	101.5488	130.9456	137.4544	96.923333	76.395	111.55214	129.35	82.28125	107.18	163.99667	156.372	314.211	494.97	103.8629							157.813148
17		T-035	231.815	1777.007	50.01	123.47	73.142	209.444	141.0814	157.8175	237.2743	171.058	125.125	159.0725	166.54	734.285							281.235763
18		T-037	89.33429	229.1669	190.97	48.5075	140.26	430.3675	135.7371	301.29	282.6086	763.21556	680.651	250.6662	893.8567								335.626354
19		T-044	154.8613	131.8114	237.4175	128.91286	276.315	938.891	465.105	936.263	345.8488	383.60111	361.28										439.480449
20		T-045	109.872	591.7871	78.54375	680.27333	111.46778	321.51667	194.6433	265.0275	659.3511	523.42429	441.26571	44.24333									344.717816
21		T-049	203.2217	160.5429	435.9957	458.71222	149.533	414.91267	721.3556	361.07417	861.6389	329.10824	977.99923										474.835088
22		T-050	55.922	215.5738	126.7175	487.39091	168.329	778.12385	1130.697	469.111	672.988	1054.5775	729.76833	2.755									552.26422
23		T-051	0	71.235	269.7675	266.14571	861.472	643.952	1386.744	287.684	631.56	924.52875	113.63										564.463919
24		T-052	55.57571	168.5569	361.1525	377.372	375.99364	424.495	730.53	758.15615	274.3611												397.108878
25		T-056	433.4	106.992	176.7714	162.9925	341.34375	314.70333	307.16	481.549	0												302.823962
26		T-058	203.485	56.746	112.345	229.48333	201.636	1153.8575	147.04	0													261.340278
27		T-060	132.1457	122.886	117.7343	158.6225	780.486	299.09	153.1643	29.09													281.390385
28		T-061	31.918	48.71429	156.76	188.92	90.502	127.344	91.19														90.3836667
29		T-063	43.76833	331.1329	285.0757	524.08	643.88	448.18909	1916.645														589.427077
30		T-069	101.0775	111.2736	142.387	186.705	634.805																191.487838
31		T-070	82.385	116.2817	155.5543	189.66833	210.68333																144.676429
32		T-071	86.24143	162.048	240.57	168.91333	199.36167																168.9
33		V-001	66.375	383.5167	189.7933	433.04286	200.44	399.24889	234.1733	169	593.55	302.33667	298.62714	662.6617	183.54	209.0475	345.7775	183.528	213.61	485.428	793.308	730.568	389.457283
34		V-008	110.4667	185.472	372.06	140.67714	368.95143	351.50875	259.6908	177.80667	679.9617	282.61909	262.92167	547.344	198.8025	425.8567	153.8986	275.965					291.104595
35		V-009	325.046	243.0225	149.3275	237.59	259.015	729.267	177.505	127.54875	238.64	118.46429	96.27	249.778	190.335	234.3425	351.08	321.673					275.277079
36		V-019	171.425	208.92	0	168.805	94.46	434.024	249.8944	382.42	300.624	700.046	589.28	125.34	504.718	207.7733	821.22						385.006552
37		V-023	140.832	441.1529	105.3025	156.1375	258.303	341.18857	162.3389	45.015	661.68	460.905	912.22929	1012.574	314.4771	695.4309	214.5967						474.502913
38		V-028	88.626	163.0343	347.3813	107.935	1734.9343	679.766	172.8886	243.93	789.56	229.57333	406.395	201.6625	952.855								504.839344
39		V-032	104.36	334.252	141.6783	151.07111	50.61	191.39727	227.49	125.91	418.2825	237.04	283.25857	292.02	307.906	159.6744							216.08
40		V-034	193.45	210.6433	104.5667	177.3375	86.065	259.19	160.972	170.8075	128.1138	696.0425	161.82	173.702	158.586	443.94							217.81
41		V-036	50.991	277.715	134.0178	265.754	402.34667	523.326	430.55	691.15615	828.4075	507.71	614.18625	58.75333									422.019298
42		V-039	122.702	132.6457	227.59	212.36375	473.71625	323.58667	290.5138	900.427	184.0186	223.20833	266.52286	288.8636	895.1333								368.303978
43		V-040	139.8283	196.39	114.6675	260.33429	106.80286	315.30417	191.955	156.1525	502.02	402.004	672.70833	617.5327	421.2933								343.1357
44		V-042	190.2733	133.0845	174.9744	191.12583	472.425	469.73333	167.9638	782.89	237.285	271.1	155.50333										282.152073
45		V-043	335.723	256.094	162.6564	350.54077	309.41333	738.3	177.422	562.73333	1268.205	481.60111	0										487.023673
46		V-046	207.5463	128.9611	266.2936	215.032	389.82143	507.23375	815.953	168.09375	212.5783	282.59286	355.92										328.367303
47		V-047	136.544	124.1429	119.4958	150.9825	189.33	621.17833	413.3617	184.5025	2095.755	295.265	196.63										363.370169
48		V-048	203.3025	115.7711	193	233.4	240.97875	131.76833	223.09	1411.25	567.4	159.66	587.13										293.30375
49		V-053	108.1633	90.095	139.5382	259.70429	280.884	317.36385	488.835	1180.7254	402.1113	527.285											451.392051
50		V-054	157.0425	263.595	368.7333	176.97429	240.01375	376.62	422.5822	219.65778	616.2583	360.16125											320.883333
51		V-055	132.9233	153.618	363.993	163.86	349.965	205.04818	698.545	252.77667	404.427	589.94545											352.939756
52		V-057	26.32	1322.45	852.0317	148.525	275.21	151.37429	283.75														452.922444
53		V-059	84.3825	128.53	185.3425	264.04	150.64	208.06444	2843.4														322.644412
54		V-062	72.59333	445.0445	313.9993	150.28615	924.40615	879.864	407.6583	0													476.623253
55		V-064	54.5075	164.6857	260.91	124.51222	466.559	290.68429	591.7914														298.753265
56		V-065	138.2517	141.3617	147.782	473.12857	290.82545	361.93111	491.828														298.85898
57		V-066	42.77667	398.2963	262.125	405.84667	339.97889	394.675	366.379														308.478167
58		V-067	212.29	116.206	343.172	102.294	621.94444																319.093226
59		V-072	80.21	79.716	210.6225	162.3075	41.45																119.476154
60		GT	149.3927	238.0126	215.6649	270.32029	352.60411	401.07981	478.5966	404.62528	493.4062	422.56024	469.69627	418.4098	472.6087	573.7107	359.7804	444.971	308.949	440.54	793.308	730.568	363.601981
61
Data

Cell Formulas
Range	Formula
I2:AD60	I2	=LET(v,A2:A4468,d,B2:B4468,s,DATA5,APIVOT(v,DATEDIF(MAP(v,LAMBDA(x,MIN(IF(x=v,d))))-90,d,"Y")+1,s,LAMBDA(x,AVERAGE(x))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DATA5	=Data!$D$2:$D$4468	I2
TEST1	=Data!$A$3335:$D$4468	I2

 

[Xlambda]

For the ones who still like to use ...IFS functions (I never use them, by default) in this case of simple use, MINIFS works fine

=LET(v,A2:A4468,d,B2:B4468,s,DATA5,APIVOT(v,DATEDIF(MINIFS(d,v,v)-90,d,"y")+1,s,LAMBDA(x,SUM(x))))

Copy of Excel_Calculate_Average_Repair_Costs_By_Age_Of_Vehicle_Episode_2627.xlsx
	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
2		PT	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	GT
3		T-004	886.14	838.26	684.04	1565.77	383.9	1004.01	58.3	1243.74	5253.58	537.7	1163.12	130.48	1999.11	2682.04	1249.46	963.46	2675.83	2814.45			26133.39
4		T-005	973.89	976.53	1035.99	1036.54	1433.34	627.27	467.6	1013	914.13	472.24	238.67	797.82	816.37	1640.24	3328.88	1065.79	1008.63	0			17846.93
5		T-006	1114.08	1028.45	635.77	267.11	463.87	311	497.11	322.83	454.72	1378.75	154.31	491.46	1007.58	1020.81	482.95	121.36					9752.16
6		T-010	1420.65	1154.96	486.79	1466.53	3560.09	3829.21	1196.41	882.44	2845.63	1712.06	1738.47	648.5	1374.64	730.9	1746.27	5300.26					30093.81
7		T-011	430.62	1060.15	808.13	2965.14	1059.39	4007.74	1150.75	1625.15	1488.05	378.06	705.45	5642.37	5841.47	3774.72	2481.14	701.89					34120.22
8		T-012	1082.09	939.6	884.88	3595.55	961.72	989.41	496.01	1541.74	1385.24	1341.27	2585.54	773.34	2051.28	8796.89	5449.14	857.22					33730.92
9		T-013	1738.5	1061.68	2111.14	2775.4	3873.5	568.35	1040.47	2929.39	1349.06	1332.49	1259.08	2243.05	1650.24	4624.79	2802.42	5326.97					36686.53
10		T-017	1275.93	1292.94	1520.96	1727.93	2828.22	3091.75	4314.83	4980.77	3342.29	3359.8	12052.25	8043.29	2025.79								49856.75
11		T-018	549.64	1193.82	623.65	432.22	2042.29	1212.15	579.87	1056.82	1535.97	589.92	368.13	984.56	1938.62	700	0						13807.66
12		T-020	1461.86	1463.54	1530.8	2442.92	1191.06	7057.97	10677.64	2547.15	8375.46	4349.29	7270.19	8903.05	10409.99	8541.4	2032.44						78254.76
13		T-022	732.96	1456.03	1292.13	1837.21	2097.9	1139.18	19691.19	2061.83	7675.98	9711.49	2923.63	3283.82	10685.13	2527.75	4072.56						71188.79
14		T-024	685.81	1555.46	1364.42	3042.08	1880.56	1221.82	2053.62	1727.89	2838.71	2106.58	1399.54	5732.49	1175.36	443.92	3237.91						30466.17
15		T-026	2096.01	1822.75	3423.79	3877.06	4081.11	402.05	5397.74	4037.48	3189.13	8408.63	5343.32	2786.85	3325.11	16702	213.65						65106.68
16		T-029	812.39	1178.51	1237.09	290.77	763.95	1561.73	905.45	658.25	535.9	983.98	781.86	3142.11	3464.79	727.04							17043.82
17		T-035	463.63	5331.02	100.02	493.88	365.71	1047.22	987.57	631.27	1660.92	855.29	250.25	636.29	832.7	2937.14							16592.91
18		T-037	625.34	2979.17	763.88	194.03	981.82	1721.47	950.16	2410.32	1978.26	6868.94	6806.51	3258.66	2681.57								32220.13
19		T-044	1238.89	922.68	949.67	902.39	1657.89	9388.91	5581.26	9362.63	2766.79	3452.41	2890.24										39113.76
20		T-045	549.36	4142.51	628.35	4081.64	1003.21	2893.65	1751.79	2120.22	5934.16	3663.97	3088.86	132.73									29990.45
21		T-049	1219.33	1123.8	3051.97	4128.41	1495.33	6223.69	6492.2	4332.89	7754.75	5594.84	12713.99										54131.2
22		T-050	279.61	2802.46	1013.74	5361.3	1683.29	10115.61	14699.06	4691.11	6729.88	8436.62	4378.61	5.51									60196.8
23		T-051	0	427.41	2158.14	1863.02	8614.72	3219.76	9707.21	2876.84	5052.48	7396.23	454.52										41770.33
24		T-052	389.03	2696.91	2889.22	5660.58	4135.93	4244.95	6574.77	9856.03	2469.25												38916.67
25		T-056	1733.6	534.96	1237.4	651.97	2730.75	1888.22	2457.28	4815.49	0												16049.67
26		T-058	1627.88	283.73	449.38	688.45	1008.18	4615.43	735.2	0													9408.25
27		T-060	925.02	614.43	824.14	1268.98	7804.86	2093.63	1072.15	29.09													14632.3
28		T-061	159.59	341	470.28	377.84	452.51	636.72	273.57														2711.51
29		T-063	262.61	4635.86	1995.53	4716.72	6438.8	4930.08	15333.16														38312.76
30		T-069	404.31	1224.01	1423.87	1493.64	2539.22																7085.05
31		T-070	494.31	697.69	1088.88	1138.01	632.05																4050.94
32		T-071	603.69	810.24	1443.42	1013.48	1196.17																5067
33		V-001	132.75	2301.1	569.38	3031.3	601.32	3593.24	702.52	507	2374.2	907.01	2090.39	3975.97	917.7	836.19	1383.11	734.11	640.83	2912.57	3966.54	3652.84	35830.07
34		V-008	662.8	927.36	1488.24	984.74	2582.66	2812.07	3116.29	1600.26	4079.77	3108.81	1577.53	2736.72	795.21	2555.14	1077.29	2207.72					32312.61
35		V-009	1625.23	972.09	597.31	950.36	2590.15	7292.67	355.01	1020.39	1670.48	829.25	577.62	1248.89	761.34	937.37	2106.48	965.02					24499.66
36		V-019	685.7	208.92	0	337.61	94.46	2170.12	2249.05	382.42	1503.12	3500.23	2946.4	376.02	2523.59	1246.64	4106.1						22330.38
37		V-023	704.16	3088.07	421.21	624.55	2583.03	2388.32	1461.05	90.03	2646.72	1843.62	12771.21	9113.17	2201.34	7649.74	1287.58						48873.8
38		V-028	443.13	1141.24	2779.05	215.87	12144.54	3398.83	1210.22	487.86	3947.8	688.72	1625.58	806.65	1905.71								30795.2
39		V-032	626.16	1671.26	850.07	1359.64	202.44	2105.37	454.98	377.73	1673.13	1659.28	1982.81	1752.12	3079.06	1437.07							19231.12
40		V-034	580.35	631.93	313.7	709.35	344.26	777.57	804.86	683.23	1024.91	2784.17	323.64	868.51	792.93	1775.76							12415.17
41		V-036	509.91	2221.72	1206.16	2657.54	4828.16	5233.26	5166.6	8985.03	6627.26	5584.81	4913.49	176.26									48110.2
42		V-039	613.51	928.52	910.36	1698.91	3789.73	1941.52	2324.11	9004.27	1288.13	1339.25	1865.66	3177.5	5370.8								34252.27
43		V-040	838.97	589.17	1376.01	1822.34	747.62	3783.65	383.91	624.61	6024.24	6030.06	4036.25	6792.86	1263.88								34313.57
44		V-042	1141.64	1463.93	1574.77	2293.51	3779.4	5636.8	1343.71	3131.56	949.14	1355.5	466.51										23136.47
45		V-043	3357.23	2560.94	1789.22	4557.03	2784.72	5906.4	887.11	5064.6	16486.66	4334.41	0										47728.32
46		V-046	1660.37	1160.65	2929.23	2150.32	2728.75	4057.87	8159.53	1344.75	1275.47	1978.15	1779.6										29224.69
47		V-047	682.72	869	1433.95	603.93	946.65	3727.07	2480.17	738.01	8383.02	1181.06	393.26										21438.84
48		V-048	813.21	1041.94	1158	466.8	1927.83	790.61	892.36	2822.5	4539.2	798.3	1174.26										16425.01
49		V-053	324.49	360.38	1534.92	1817.93	1404.42	4125.73	3910.68	15349.43	3216.89	3163.71											35208.58
50		V-054	628.17	2108.76	2212.4	1238.82	1920.11	2636.34	3803.24	1976.92	3697.55	2881.29											23103.6
51		V-055	797.54	768.09	3639.93	983.16	2099.79	2255.53	5588.36	2274.99	4044.27	6489.4											28941.06
52		V-057	131.6	9257.15	5112.19	891.15	1376.05	1059.62	2553.75														20381.51
53		V-059	337.53	899.71	741.37	528.08	903.84	1872.58	5686.8														10969.91
54		V-062	653.34	4895.49	4395.99	1953.72	12017.28	13197.96	2445.95	0													39559.73
55		V-064	218.03	1152.8	1304.55	1120.61	4665.59	2034.79	4142.54														14638.91
56		V-065	829.51	848.17	738.91	3311.9	3199.08	3257.38	2459.14														14644.09
57		V-066	384.99	3186.37	2621.25	2435.08	3059.81	3157.4	3663.79														18508.69
58		V-067	1486.03	581.03	1715.86	511.47	5597.5																9891.89
59		V-072	401.05	398.58	842.49	1298.46	165.8																3106.38
60		GT	47506.89	92824.93	82383.99	101910.75	148446.33	169255.68	181388.1	124219.96	150982.3	123387.59	107090.75	78661.05	70891.31	72287.55	37057.38	18243.8	4325.29	5727.02	3966.54	3652.84	1624210.05
61
Data

Cell Formulas
Range	Formula
I2:AD60	I2	=LET(v,A2:A4468,d,B2:B4468,s,DATA5,APIVOT(v,DATEDIF(MINIFS(d,v,v)-90,d,"y")+1,s,LAMBDA(x,SUM(x))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
DATA5	=Data!$D$2:$D$4468	I2
TEST1	=Data!$A$3335:$D$4468	I2

 

[Xlambda]

Finally, after almost 4 years of "advocating" for a function that does pivoting tables I am happy to use Excel native functions instead of my ones. (Hope that a native UNPIVOT will follow)
Check out latest ExcelIsFun Mike's YT

Tricks to replace COUNTIFS using GROUPBY/PIVOTBY

EMT1847-1849.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			Mike's spreadsheet from downloadable link
2			Extra Column	Post	Time to Assemble Product at Post (Seconds)		Goal: Count how many times an Assemby Line Post fell below required <10 second assembly time.
3				1	9		This is an AND Logical Test: Post has to equal given post AND Seconds <10
4				2	9.2		** This is an example of where a PivotTable would be harder to create
5				3	8.3		because our condition uses a comparative operator (we have a hurdle as a condition).
6				4	8.3
7				5	8.8		Hurdle in Seconds:	10		Alternative
8				6	10
9				7	10.7		Post	Count		=GROUPBY(D3:D590,N(E3:E590<H7),SUM)
10				8	8		1			1	38
11				9	9.1		2			2	41
12				10	10.7		3			3	45
13				11	9.9		4			4	40
14				12	8.5		5			5	45
15				1	10		6			6	42
16				2	8.6		7			7	17
17				3	9.1		8			8	48
18				4	8.3		9			9	45
19				5	8.1		10			10	42
20				6	10.4		11			11	19
21				7	9.1		12			12	39
22				8	8.4					Total	461
23				9	8.3
24				10	11			=PIVOTBY(,D3:D590,N(E3:E590<H7),SUM)
25				11	9.7			1	2	3	4	5	6	7	8	9	10	11	12	Total
26				12	9.5			38	41	45	40	45	42	17	48	45	42	19	39	461
27				1	8.3
28				2	9.1
29				3	8.5
30				4	8
31				5	8.2
32				6	8.9
33				7	11.5
1848

Cell Formulas
Range	Formula
J9,H24	J9	=FORMULATEXT(J10)
J10:K22	J10	=GROUPBY(D3:D590,N(E3:E590<H7),SUM)
H25:T26	H25	=PIVOTBY(,D3:D590,N(E3:E590<H7),SUM)
Dynamic array formulas.

EMT1847-1849.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						Pivotby can do Groupby						COUNTIFS using groupby in an intuitive simple way
2												simple count with groupby does not need other column
3						=GROUPBY(B4:B24,D4:D24,SUM)						=LET(x,B4:B24,GROUPBY(x,x,COUNTA))
4		a	x	10		a	276					a	7
5		b	y	40		b	336					b	5
6		a	y	20		c	252					c	5
7		a	x	31		d	151					d	4
8		d	z	14		Total	1015					Total	21
9		d	z	15
10		b	y	78		Pivotby can do Groupby,ignoring clm fields						counting filtered data, only values <50
11		b	x	97								classic
12		c	x	86		=PIVOTBY(B4:B24,,D4:D24,SUM)						=GROUPBY(B4:B24,D4:D24,COUNT,,,,D4:D24<50)
13		a	z	57		a	276					a	5
14		c	x	40		b	336					b	2
15		c	y	32		c	252					c	3
16		a	z	39		d	151					d	3
17		b	z	45		Total	1015					Total	13
18		a	x	32
19		b	x	76		Horizontal Groupby if we ignore row fields						alternative
20		a	y	87
21		d	y	45		=PIVOTBY(,B4:B24,D4:D24,SUM)						=GROUPBY(B4:B24,N(D4:D24<50),SUM)
22		d	x	77		a	b	c	d	Total		a	5
23		c	y	28		276	336	252	151	1015		b	2		=PIVOTBY(B4:B24,C4:C24,N(D4:D24<50),SUM)
24		c	y	66								c	3			x	y	z	Total
25												d	3		a	3	1	1	5
26												Total	13		b	0	1	1	2
27															c	1	2		3
28															d	0	1	2	3
29															Total	4	5	4	13
30
31												2 conditions values <50 only for x and y
32
33												=GROUPBY(B4:B24,N((D4:D24<50)*(C4:C24<"z")),SUM)
34												a	4
35												b	1
36												c	3
37												d	1
38												Total	9
39
Sheet2

Cell Formulas
Range	Formula
F3,L3,L33,O23,L21,F21,L12,F12	F3	=FORMULATEXT(F4)
F4:G8	F4	=GROUPBY(B4:B24,D4:D24,SUM)
L4:M8	L4	=LET(x,B4:B24,GROUPBY(x,x,COUNTA))
F13:G17	F13	=PIVOTBY(B4:B24,,D4:D24,SUM)
L13:M17	L13	=GROUPBY(B4:B24,D4:D24,COUNT,,,,D4:D24<50)
F22:J23	F22	=PIVOTBY(,B4:B24,D4:D24,SUM)
L22:M26	L22	=GROUPBY(B4:B24,N(D4:D24<50),SUM)
O24:S29	O24	=PIVOTBY(B4:B24,C4:C24,N(D4:D24<50),SUM)
L34:M38	L34	=GROUPBY(B4:B24,N((D4:D24<50)*(C4:C24<"z")),SUM)
Dynamic array formulas.

 

[mgirvin]

Thanks for the shout out, Xlambda!!!!! : ) : )

All your formulas in this post are wonderful, but this is magnificent:

=GROUPBY(D3:D590,N(E3:E590<H7),SUM)

Thanks for that clever formula. Many years ago for my array formula book, I timed methods for converting T and F to 1 and 0. --, +0, *1, /1, N function and so on and double negative was the fastest. I have not timed it with the new engine, but here is an alternative to your alternative:

=GROUPBY(D3:D590,--(E3:E590<H7),SUM)

I added these fabulous formula to the download file.

Thanks, Teammate : )

Go Team!!!!