ASCAN

[Xlambda]

ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)

LAMBDA 1.1.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
2	sample 1			a		=ASCAN(A3:D6)					=ASCAN(A3:D6,-1)					=ASCAN(A3:D6,1)
3	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
4	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
5	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
6	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
7
8						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
9	sample 2			a		=ASCAN(A10#)					=ASCAN(A10#,-1)					=ASCAN(A10#,1)
10	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
11	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
12	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
13	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
14	17	18	19	20		153	171	190	210		17	35	54	74		45	50	55	60
15	21	22	23	24		231	253	276	300		21	43	66	90		66	72	78	84
16
17	Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18	adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post

Cell Formulas
Range	Formula
F2,P9,K9,F9,P2,K2	F2	=FORMULATEXT(F3)
F3:I6	F3	=ASCAN(A3:D6)
K3:N6	K3	=ASCAN(A3:D6,-1)
P3:S6	P3	=ASCAN(A3:D6,1)
A10:D15	A10	=SEQUENCE(6,4)
F10:I15	F10	=ASCAN(A10#)
K10:N15	K10	=ASCAN(A10#,-1)
P10:S15	P10	=ASCAN(A10#,1)
Dynamic array formulas.

 

[Xlambda]

Inspired by latest Mr.Excel YT (7-Apr-2022): Find Next True And The Matching Records Above And Below - 2480
It's not about reduce or scan techniques, is about defining an "inside" lambda that can be called by an argument value of main function.
Challenge description:
"Michele has 46000 rows of data. Each ID has several records in a row. Many records are marked as "Changed". When there is a change, Michele needs to check the record marked, plus the matching IDs just above and below. In this video, I try Find All and 2 Advanced Filters. I bet you have something better."
A lambda that solves the challenge:
IDIXFLT(ar,[n] ID Index Filter function, can handle unsorted array, or TRUE dups for same ID's
ar: array
[n]: integer
- if n omitted or n=0, function returns total nr. of matches and the list of all matched ID's
- if 1<=n<=t (total nr. of unique matches), function returns as 1st clm, an array of rows indexes stacked horizontally to records extraction (all fields) of respective "n" value
- if n>t => n=t
- if n<0 => n=abs( n )

=LAMBDA(ar, [n],
    LET(
        a, IF(ar = "", "", ar),
        id, INDEX(a, , 2),
        tc, INDEX(a, , 4),
        ft, IF(tc = TRUE, 1, 0),
        f, SORT(UNIQUE(FILTER(id, ft))),
        t, ROWS(f),
        s, SEQUENCE(ROWS(a)),
        z, LAMBDA(k,
            LET(
                x, INDEX(f, k),
                y, id = x,
                SORT(HSTACK(FILTER(s, y), FILTER(a, y)))
            )
        ),
        IF(n, z(MIN(ABS(n), t)), IFNA(HSTACK(t, f), ""))
    )
)

part of defining the lambda z(k), variable "z" :
z,LAMBDA(k,LET(x,INDEX(f,k),y,id=x,SORT(HSTACK(FILTER(s,y),FILTER(a,y)))))
part of calling z( n ), where "n" is one of main's function argument :
z(MIN(ABS( n ),t))

Mr.Excel 2480 challenge 2nd draft.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
1	sample array, sorted					sample array, unsorted
2	no TRUE dups					has TRUE dups for same ID					index array			omitted or 0			n,1						n,3
3											=SEQUENCE(ROWS(F4:I35))			=IDIXFLT(F4:I35)			=IDIXFLT(F4:I35,1)						=IDIXFLT(F4:I35,3)
4	1761	Q7091	352			1763	Q7091	352			1			5	E3736		9	2031	E3736	171			12	2124	L6272	643	TRUE
5	1762	Q7091	534			1764	Q7091	534	TRUE		2				H7453		10	2032	E3736	388	TRUE		13	2125	L6272	565
6	1763	Q7091	565			1765	Q7091	565			3				L6272		11	2033	E3736	196
7	1764	Q7091	631	TRUE		1922	X3914	631			4				Q7091		↑↑↑						n,4
8	1765	Q7091	502			1923	X3914	502	TRUE		5				X3914		first clm, all rows indexes of first id match (for n=1)						=IDIXFLT(F4:I35,4)
9	1766	Q7091	219			1924	X3914	219			6			↑↑									1	1763	Q7091	352
10	1917	X3914	213			1967	H7453	213	TRUE		7			top left cell, total nr. matches and their values									2	1764	Q7091	534	TRUE
11	1918	X3914	248			1968	H7453	248			8						n,2						3	1765	Q7091	565
12	1919	X3914	171			2031	E3736	171			9						=IDIXFLT(F4:I35,2)						14	1766	Q7091	365
13	1920	X3914	388			2032	E3736	388	TRUE		10						7	1967	H7453	213	TRUE		27	1761	Q7091	291
14	1921	X3914	196			2033	E3736	196			11						8	1968	H7453	248			28	1762	Q7091	457
15	1922	X3914	643			2124	L6272	643	TRUE		12						17	1972	H7453	721
16	1923	X3914	565	TRUE		2125	L6272	565			13						18	1973	H7453	444			n,5
17	1924	X3914	365			1766	Q7091	365			14						19	1974	H7453	491			=IDIXFLT(F4:I35,5)
18	1967	H7453	708	TRUE		1917	X3914	708			15						20	1975	H7453	244			4	1922	X3914	631
19	1968	H7453	605			1918	X3914	605			16						24	1969	H7453	623			5	1923	X3914	502	TRUE
20	1969	H7453	721			1972	H7453	721			17		TRUE				25	1970	H7453	614			6	1924	X3914	219
21	1970	H7453	444			1973	H7453	444			18		duplicates TRUE				26	1971	H7453	239			15	1917	X3914	708
22	1971	H7453	491			1974	H7453	491			19		for same ID "X3914"				29	1976	H7453	351			16	1918	X3914	605
23	1972	H7453	244			1975	H7453	244			20						30	1977	H7453	305			21	1919	X3914	568
24	1973	H7453	568			1919	X3914	568			21						31	1978	H7453	683			22	1920	X3914	331	TRUE
25	1974	H7453	331			1920	X3914	331	TRUE		22						32	1979	H7453	395			23	1921	X3914	236
26	1975	H7453	236			1921	X3914	236			23
27	1976	H7453	623			1969	H7453	623			24			calling the function for sorted array A4:D35
28	1977	H7453	614			1970	H7453	614			25			n,omitted			n,5						n,-100 => n=abs(n)=100 > 5 => n=5
29	1978	H7453	239			1971	H7453	239			26			=IDIXFLT(A4:D35)			=IDIXFLT(A4:D35,5)						=IDIXFLT(F4:I35,-100)
30	1979	H7453	291			1761	Q7091	291			27			5	E3736		7	1917	X3914	213			4	1922	X3914	631
31	2031	E3736	457			1762	Q7091	457			28				H7453		8	1918	X3914	248			5	1923	X3914	502	TRUE
32	2032	E3736	351	TRUE		1976	H7453	351			29				L6272		9	1919	X3914	171			6	1924	X3914	219
33	2033	E3736	305			1977	H7453	305			30				Q7091		10	1920	X3914	388			15	1917	X3914	708
34	2124	L6272	683	TRUE		1978	H7453	683			31				X3914		11	1921	X3914	196			16	1918	X3914	605
35	2125	L6272	395			1979	H7453	395			32						12	1922	X3914	643			21	1919	X3914	568
36																	13	1923	X3914	565	TRUE		22	1920	X3914	331	TRUE
37																	14	1924	X3914	365			23	1921	X3914	236
38
39																		As we see, both arrays, sorted and unsorted return same values, but of course,
40																		as 1st clm, the array of row indexes, is different
41																		Also, notice that sorted array has no TRUE dups.
42
last draft

Cell Formulas
Range	Formula
K3,N3,Q3,W29,N29,Q29,W17,Q12,W8,W3	K3	=FORMULATEXT(K4)
K4:K35	K4	=SEQUENCE(ROWS(F4:I35))
N4:O8	N4	=IDIXFLT(F4:I35)
Q4:U6	Q4	=IDIXFLT(F4:I35,1)
W4:AA5	W4	=IDIXFLT(F4:I35,3)
W9:AA14	W9	=IDIXFLT(F4:I35,4)
Q13:U25	Q13	=IDIXFLT(F4:I35,2)
W18:AA25	W18	=IDIXFLT(F4:I35,5)
N30:O34	N30	=IDIXFLT(A4:D35)
Q30:U37	Q30	=IDIXFLT(A4:D35,5)
W30:AA37	W30	=IDIXFLT(F4:I35,-100)
Dynamic array formulas.

 

[Xlambda]

Mr.Excel 2480 challenge 2nd draft.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
1	sample					simulation for 100000 rows, dealing with whole array comparison and no iterations involved, function speed is lighting fast.
2	1761	Q7091	352			=SEQUENCE(100000)																		check results array nr. rows
3	1762	Q7091	534			↓↓↓↓	=MOD(F4#-1,ROWS(A2:D33))+1		=INDEX(IF(A2:D33="","",A2:D33),G4#,SEQUENCE(,4))						=IDIXFLT(I4#)			=IDIXFLT(I4#,5)						=ROWS(R4#)
4	1763	Q7091	565			1	1		1761	Q7091	352				5	E3736		7	1917	X3914	213			25000
5	1764	Q7091	631	TRUE		2	2		1762	Q7091	534					H7453		8	1918	X3914	248
6	1765	Q7091	502			3	3		1763	Q7091	565					L6272		9	1919	X3914	171
7	1766	Q7091	219			4	4		1764	Q7091	631	TRUE				Q7091		10	1920	X3914	388
8	1917	X3914	213			5	5		1765	Q7091	502					X3914		11	1921	X3914	196
9	1918	X3914	248			6	6		1766	Q7091	219							12	1922	X3914	643
10	1919	X3914	171			7	7		1917	X3914	213			check rows				13	1923	X3914	565	TRUE
11	1920	X3914	388			8	8		1918	X3914	248			=ROWS(I4#)				14	1924	X3914	365
12	1921	X3914	196			9	9		1919	X3914	171			100000				39	1917	X3914	213
13	1922	X3914	643			10	10		1920	X3914	388							40	1918	X3914	248
14	1923	X3914	565	TRUE		11	11		1921	X3914	196							41	1919	X3914	171
15	1924	X3914	365			12	12		1922	X3914	643							42	1920	X3914	388
16	1967	H7453	708	TRUE		13	13		1923	X3914	565	TRUE						43	1921	X3914	196
17	1968	H7453	605			14	14		1924	X3914	365							44	1922	X3914	643
18	1969	H7453	721			15	15		1967	H7453	708	TRUE						45	1923	X3914	565	TRUE
19	1970	H7453	444			16	16		1968	H7453	605							46	1924	X3914	365
20	1971	H7453	491			17	17		1969	H7453	721							71	1917	X3914	213
21	1972	H7453	244			18	18		1970	H7453	444							72	1918	X3914	248
22	1973	H7453	568			19	19		1971	H7453	491							73	1919	X3914	171
23	1974	H7453	331			20	20		1972	H7453	244							74	1920	X3914	388
24	1975	H7453	236			21	21		1973	H7453	568							75	1921	X3914	196
25	1976	H7453	623			22	22		1974	H7453	331							76	1922	X3914	643
26	1977	H7453	614			23	23		1975	H7453	236							77	1923	X3914	565	TRUE
27	1978	H7453	239			24	24		1976	H7453	623							78	1924	X3914	365
28	1979	H7453	291			25	25		1977	H7453	614							103	1917	X3914	213
29	2031	E3736	457			26	26		1978	H7453	239							104	1918	X3914	248
30	2032	E3736	351	TRUE		27	27		1979	H7453	291							105	1919	X3914	171
31	2033	E3736	305			28	28		2031	E3736	457							106	1920	X3914	388
32	2124	L6272	683	TRUE		29	29		2032	E3736	351	TRUE						107	1921	X3914	196
33	2125	L6272	395			30	30		2033	E3736	305							108	1922	X3914	643
34						31	31		2124	L6272	683	TRUE						109	1923	X3914	565	TRUE
35						32	32		2125	L6272	395							110	1924	X3914	365
36						33	1		1761	Q7091	352							135	1917	X3914	213
37						34	2		1762	Q7091	534							136	1918	X3914	248
38						35	3		1763	Q7091	565							137	1919	X3914	171
39						36	4		1764	Q7091	631	TRUE						138	1920	X3914	388
40						37	5		1765	Q7091	502							139	1921	X3914	196
41						38	6		1766	Q7091	219							140	1922	X3914	643
42						39	7		1917	X3914	213							141	1923	X3914	565	TRUE
43						40	8		1918	X3914	248							142	1924	X3914	365
44						41	9		1919	X3914	171							167	1917	X3914	213
45						42	10		1920	X3914	388							168	1918	X3914	248
46						43	11		1921	X3914	196							169	1919	X3914	171
47						44	12		1922	X3914	643							170	1920	X3914	388
48						45	13		1923	X3914	565	TRUE						171	1921	X3914	196
49						46	14		1924	X3914	365							172	1922	X3914	643
50						47	15		1967	H7453	708	TRUE						173	1923	X3914	565	TRUE
Sheet1

Cell Formulas
Range	Formula
F2	F2	=FORMULATEXT(F4)
G3,I3,O3,N11,X3,R3	G3	=FORMULATEXT(G4)
F4:F100003	F4	=SEQUENCE(100000)
G4:G100003	G4	=MOD(F4#-1,ROWS(A2:D33))+1
I4:L100003	I4	=INDEX(IF(A2:D33="","",A2:D33),G4#,SEQUENCE(,4))
O4:P8	O4	=IDIXFLT(I4#)
R4:V25003	R4	=IDIXFLT(I4#,5)
X4	X4	=ROWS(R4#)
N12	N12	=ROWS(I4#)
Dynamic array formulas.

 

[Xlambda]

Inspired by latest Mr.Excel YT (14-Apr-2022): Count Number Of Weekdays Per Month using Excel - 2481
Task: "Ronak asks: How can I use Excel to find out how many weekdays per month for the whole year?"
2 simple lambdas to solve it using MAKEARRAY , main core function used to reproduce PT functionality in general.
DNY( y ) Day Names/Year y: year 4 digits

=LAMBDA(y,
    LET(
        f, DATE(y, 1, 1),
        l, DATE(y, 12, 31),
        s, SEQUENCE(l - f + 1, , f),
        MAKEARRAY(12, 7, LAMBDA(r, c, SUM((c = WEEKDAY(s, 1)) * (r = MONTH(s)))))
    )
)

PTDNY( y ) Pivot Table Day Names/Year, calls DNY( y )

=LAMBDA(y,
    LET(
        g, "GT",
        a, DNY(y),
        VSTACK(
            HSTACK(y, TEXT(SEQUENCE(, 7), "ddd"), g),
            HSTACK(TEXT(SEQUENCE(12, , , 31), "mmm"), a, BYROW(a, LAMBDA(x, SUM(x)))),
            HSTACK(g, BYCOL(a, LAMBDA(x, SUM(x))), SUM(a))
        )
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		inner array calculation								entire PT
3										y,2021
4		y,2021								=PTDNY(2021)
5		=DNY(2021)								2021	Sun	Mon	Tue	Wed	Thu	Fri	Sat	GT
6		5	4	4	4	4	5	5		Jan	5	4	4	4	4	5	5	31
7		4	4	4	4	4	4	4		Feb	4	4	4	4	4	4	4	28
8		4	5	5	5	4	4	4		Mar	4	5	5	5	4	4	4	31
9		4	4	4	4	5	5	4		Apr	4	4	4	4	5	5	4	30
10		5	5	4	4	4	4	5		May	5	5	4	4	4	4	5	31
11		4	4	5	5	4	4	4		Jun	4	4	5	5	4	4	4	30
12		4	4	4	4	5	5	5		Jul	4	4	4	4	5	5	5	31
13		5	5	5	4	4	4	4		Aug	5	5	5	4	4	4	4	31
14		4	4	4	5	5	4	4		Sep	4	4	4	5	5	4	4	30
15		5	4	4	4	4	5	5		Oct	5	4	4	4	4	5	5	31
16		4	5	5	4	4	4	4		Nov	4	5	5	4	4	4	4	30
17		4	4	4	5	5	5	4		Dec	4	4	4	5	5	5	4	31
18										GT	52	52	52	52	52	53	52	365
19
20										y,2020 (2020 was a leap year)
21		y,2020								=PTDNY(2020)
22		=DNY(2020)								2020	Sun	Mon	Tue	Wed	Thu	Fri	Sat	GT
23		4	4	4	5	5	5	4		Jan	4	4	4	5	5	5	4	31
24		4	4	4	4	4	4	5		Feb	4	4	4	4	4	4	5	29
25		5	5	5	4	4	4	4		Mar	5	5	5	4	4	4	4	31
26		4	4	4	5	5	4	4		Apr	4	4	4	5	5	4	4	30
27		5	4	4	4	4	5	5		May	5	4	4	4	4	5	5	31
28		4	5	5	4	4	4	4		Jun	4	5	5	4	4	4	4	30
29		4	4	4	5	5	5	4		Jul	4	4	4	5	5	5	4	31
30		5	5	4	4	4	4	5		Aug	5	5	4	4	4	4	5	31
31		4	4	5	5	4	4	4		Sep	4	4	5	5	4	4	4	30
32		4	4	4	4	5	5	5		Oct	4	4	4	4	5	5	5	31
33		5	5	4	4	4	4	4		Nov	5	5	4	4	4	4	4	30
34		4	4	5	5	5	4	4		Dec	4	4	5	5	5	4	4	31
35										GT	52	52	52	53	53	52	52	366
36
37										y,2022
38										=PTDNY(2022)
39										2022	Sun	Mon	Tue	Wed	Thu	Fri	Sat	GT
40										Jan	5	5	4	4	4	4	5	31
41										Feb	4	4	4	4	4	4	4	28
42										Mar	4	4	5	5	5	4	4	31
43										Apr	4	4	4	4	4	5	5	30
44										May	5	5	5	4	4	4	4	31
45										Jun	4	4	4	5	5	4	4	30
46										Jul	5	4	4	4	4	5	5	31
47										Aug	4	5	5	5	4	4	4	31
48										Sep	4	4	4	4	5	5	4	30
49										Oct	5	5	4	4	4	4	5	31
50										Nov	4	4	5	5	4	4	4	30
51										Dec	4	4	4	4	5	5	5	31
52										GT	52	52	52	52	52	52	53	365
53
DNY

Cell Formulas
Range	Formula
J4,J38,B22,J21,B5	J4	=FORMULATEXT(J5)
J5:R18	J5	=PTDNY(2021)
B6:H17	B6	=DNY(2021)
J22:R35	J22	=PTDNY(2020)
B23:H34	B23	=DNY(2020)
J39:R52	J39	=PTDNY(2022)
Dynamic array formulas.

 

[Xlambda]

If we need to change 1st weekday from Sun to Mon, 2 minor changes (in bold)
DNY( y ): .....SUM((c=WEEKDAY(s,2))*(r=MONTH(s)))))))

=LAMBDA(y,
    LET(
        f, DATE(y, 1, 1),
        l, DATE(y, 12, 31),
        s, SEQUENCE(l - f + 1, , f),
        MAKEARRAY(12, 7, LAMBDA(r, c, SUM((c = WEEKDAY(s, 2)) * (r = MONTH(s)))))
    )
)

PTDNY( y ): ....TEXT(SEQUENCE(,7)+1,"ddd").....

=LAMBDA(y,
    LET(
        g, "GT",
        a, DNY(y),
        VSTACK(
            HSTACK(y, TEXT(SEQUENCE(, 7) + 1, "ddd"), g),
            HSTACK(TEXT(SEQUENCE(12, , , 31), "mmm"), a, BYROW(a, LAMBDA(x, SUM(x)))),
            HSTACK(g, BYCOL(a, LAMBDA(x, SUM(x))), SUM(a))
        )
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2		weekdays or day names sequence			inner array calculation								entire PT
3		1st day Sun											y,2021
4		=TEXT(SEQUENCE(7),"ddd")			y,2021								=PTDNY(2021)
5		Sun			=DNY(2021)								2021	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT
6		Mon			4	4	4	4	5	5	5		Jan	4	4	4	4	5	5	5	31
7		Tue			4	4	4	4	4	4	4		Feb	4	4	4	4	4	4	4	28
8		Wed			5	5	5	4	4	4	4		Mar	5	5	5	4	4	4	4	31
9		Thu			4	4	4	5	5	4	4		Apr	4	4	4	5	5	4	4	30
10		Fri			5	4	4	4	4	5	5		May	5	4	4	4	4	5	5	31
11		Sat			4	5	5	4	4	4	4		Jun	4	5	5	4	4	4	4	30
12					4	4	4	5	5	5	4		Jul	4	4	4	5	5	5	4	31
13		1st day Mon			5	5	4	4	4	4	5		Aug	5	5	4	4	4	4	5	31
14		=TEXT(SEQUENCE(7)+1,"ddd")			4	4	5	5	4	4	4		Sep	4	4	5	5	4	4	4	30
15		Mon			4	4	4	4	5	5	5		Oct	4	4	4	4	5	5	5	31
16		Tue			5	5	4	4	4	4	4		Nov	5	5	4	4	4	4	4	30
17		Wed			4	4	5	5	5	4	4		Dec	4	4	5	5	5	4	4	31
18		Thu											GT	52	52	52	52	53	52	52	365
19		Fri
20		Sat											y,2020 (2020 was a leap year)
21		Sun			y,2020								=PTDNY(2020)
22					=DNY(2020)								2020	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT
23		month sequence			4	4	5	5	5	4	4		Jan	4	4	5	5	5	4	4	31
24		=TEXT(SEQUENCE(12,,,31),"mmm")			4	4	4	4	4	5	4		Feb	4	4	4	4	4	5	4	29
25		Jan			5	5	4	4	4	4	5		Mar	5	5	4	4	4	4	5	31
26		Feb			4	4	5	5	4	4	4		Apr	4	4	5	5	4	4	4	30
27		Mar			4	4	4	4	5	5	5		May	4	4	4	4	5	5	5	31
28		Apr			5	5	4	4	4	4	4		Jun	5	5	4	4	4	4	4	30
29		May			4	4	5	5	5	4	4		Jul	4	4	5	5	5	4	4	31
30		Jun			5	4	4	4	4	5	5		Aug	5	4	4	4	4	5	5	31
31		Jul			4	5	5	4	4	4	4		Sep	4	5	5	4	4	4	4	30
32		Aug			4	4	4	5	5	5	4		Oct	4	4	4	5	5	5	4	31
33		Sep			5	4	4	4	4	4	5		Nov	5	4	4	4	4	4	5	30
34		Oct			4	5	5	5	4	4	4		Dec	4	5	5	5	4	4	4	31
35		Nov											GT	52	52	53	53	52	52	52	366
36		Dec
37													y,2022
38													=PTDNY(2022)
39													2022	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT
40													Jan	5	4	4	4	4	5	5	31
41													Feb	4	4	4	4	4	4	4	28
42													Mar	4	5	5	5	4	4	4	31
43													Apr	4	4	4	4	5	5	4	30
44													May	5	5	4	4	4	4	5	31
45													Jun	4	4	5	5	4	4	4	30
46													Jul	4	4	4	4	5	5	5	31
47													Aug	5	5	5	4	4	4	4	31
48													Sep	4	4	4	5	5	4	4	30
49													Oct	5	4	4	4	4	5	5	31
50													Nov	4	5	5	4	4	4	4	30
51													Dec	4	4	4	5	5	5	4	31
52													GT	52	52	52	52	52	53	52	365
53
DNY 2

Cell Formulas
Range	Formula
B4,M38,B24,E22,M21,B14,E5,M4	B4	=FORMULATEXT(B5)
B5:B11	B5	=TEXT(SEQUENCE(7),"ddd")
M5:U18	M5	=PTDNY(2021)
E6:K17	E6	=DNY(2021)
B15:B21	B15	=TEXT(SEQUENCE(7)+1,"ddd")
M22:U35	M22	=PTDNY(2020)
E23:K34	E23	=DNY(2020)
B25:B36	B25	=TEXT(SEQUENCE(12,,,31),"mmm")
M39:U52	M39	=PTDNY(2022)
Dynamic array formulas.

 

[Xlambda]

On the comments section Laza Lazarevic asked a good question:
"How to exclude the holidays?"
These are the new functions that take holidays into consideration:
EHDNY(y,[h]) Exclude Holidays Day Names/Year (y: year ; [h]: holidays array)

=LAMBDA(y, [h],
    LET(
        f, DATE(y, 1, 1),
        l, DATE(y, 12, 31),
        d, SEQUENCE(l - f + 1, , f),
        s, IF(ISOMITTED(h), d, FILTER(d, NOT(IFNA(XMATCH(d, h), 0)))),
        MAKEARRAY(12, 7, LAMBDA(r, c, SUM((c = WEEKDAY(s, 2)) * (r = MONTH(s)))))
    )
)

PTEHDNY(y,[h]) (we only must add the extra argument "h" and replace DNY( y ) in the formula with EHDNY(y,h) )

=LAMBDA(y, [h],
    LET(
        g, "GT",
        a, EHDNY(y, h),
        VSTACK(
            HSTACK(y, TEXT(SEQUENCE(, 7) + 1, "ddd"), g),
            HSTACK(TEXT(SEQUENCE(12, , , 31), "mmm"), a, BYROW(a, LAMBDA(x, SUM(x)))),
            HSTACK(g, BYCOL(a, LAMBDA(x, SUM(x))), SUM(a))
        )
    )
)

Book1
	A	B	C	D	E	F	G	H	I	J
1	Exclude holidays.EHDNY(y,[h]) function concept.
2
3		sample date array		holydays array
4		=SEQUENCE(31,,"1-1-22")				=IFNA(XMATCH(B5#,D5:D12),0)		=FILTER(B5#,NOT(F5#))
5		01-01-22		14-01-22		0		01-01-22
6		02-01-22		15-01-22		0		02-01-22
7		03-01-22		16-01-22		0		03-01-22
8		04-01-22		17-01-22		0		04-01-22
9		05-01-22		18-01-22		0		05-01-22
10		06-01-22		19-01-22		0		06-01-22
11		07-01-22		29-01-22		0		07-01-22
12		08-01-22		31-01-22		0		08-01-22
13		09-01-22				0		09-01-22
14		10-01-22				0		10-01-22
15		11-01-22				0		11-01-22
16		12-01-22				0		12-01-22
17		13-01-22				0		13-01-22
18		14-01-22				1		20-01-22
19		15-01-22				2		21-01-22
20		16-01-22				3		22-01-22
21		17-01-22				4		23-01-22
22		18-01-22				5		24-01-22
23		19-01-22				6		25-01-22
24		20-01-22				0		26-01-22
25		21-01-22				0		27-01-22
26		22-01-22				0		28-01-22
27		23-01-22				0		30-01-22
28		24-01-22				0
29		25-01-22				0
30		26-01-22				0
31		27-01-22				0
32		28-01-22				0
33		29-01-22				7
34		30-01-22				0
35		31-01-22				8
36
DNY 4

Cell Formulas
Range	Formula
B4,F4,H4	B4	=FORMULATEXT(B5)
B5:B35	B5	=SEQUENCE(31,,"1-1-22")
F5:F35	F5	=IFNA(XMATCH(B5#,D5:D12),0)
H5:H27	H5	=FILTER(B5#,NOT(F5#))
Dynamic array formulas.

 

[Xlambda]

Book1
	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
1		2021-2022		y,2021,h,omitted										y,2021,h,B3:B40
2		holydays array		=PTEHDNY(2021)										=PTEHDNY(2021,B3:B40)										check results
3		01-01-21		2021	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT		2021	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT		nr. holydays 2021
4		02-01-21		Jan	4	4	4	4	5	5	5	31		Jan	4	4	4	4	4	4	4	28		=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2021))
5		03-01-21		Feb	4	4	4	4	4	4	4	28		Feb	4	4	4	4	4	4	4	28		19
6		15-08-21		Mar	5	5	5	4	4	4	4	31		Mar	5	5	5	4	4	4	4	31		=L16-V16
7		16-08-21		Apr	4	4	4	5	5	4	4	30		Apr	4	4	4	5	5	4	4	30		19
8		17-08-21		May	5	4	4	4	4	5	5	31		May	5	4	4	4	4	5	5	31
9		18-08-21		Jun	4	5	5	4	4	4	4	30		Jun	4	5	5	4	4	4	4	30
10		19-08-21		Jul	4	4	4	5	5	5	4	31		Jul	4	4	4	5	5	5	4	31
11		20-08-21		Aug	5	5	4	4	4	4	5	31		Aug	4	4	3	3	3	3	3	23
12		21-08-21		Sep	4	4	5	5	4	4	4	30		Sep	4	4	5	5	4	4	4	30
13		22-08-21		Oct	4	4	4	4	5	5	5	31		Oct	4	4	4	4	5	5	5	31
14		24-12-21		Nov	5	5	4	4	4	4	4	30		Nov	5	5	4	4	4	4	4	30
15		25-12-21		Dec	4	4	5	5	5	4	4	31		Dec	3	3	4	4	3	3	3	23
16		26-12-21		GT	52	52	52	52	53	52	52	365		GT	50	50	50	50	49	49	48	346
17		27-12-21
18		28-12-21		y,2022,h,omitted										y,2022,h,B3:B40
19		29-12-21		=PTEHDNY(2022)										=PTEHDNY(2022,B3:B40)										check results
20		30-12-21		2022	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT		2022	Mon	Tue	Wed	Thu	Fri	Sat	Sun	GT		nr. holydays 2022
21		31-12-21		Jan	5	4	4	4	4	5	5	31		Jan	4	4	4	4	4	4	4	28		=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2022))
22		01-01-22		Feb	4	4	4	4	4	4	4	28		Feb	4	4	4	4	4	4	4	28		19
23		02-01-22		Mar	4	5	5	5	4	4	4	31		Mar	4	5	5	5	4	4	4	31		=L33-V33
24		03-01-22		Apr	4	4	4	4	5	5	4	30		Apr	4	4	4	4	5	5	4	30		19
25		15-08-22		May	5	5	4	4	4	4	5	31		May	5	5	4	4	4	4	5	31
26		16-08-22		Jun	4	4	5	5	4	4	4	30		Jun	4	4	5	5	4	4	4	30
27		17-08-22		Jul	4	4	4	4	5	5	5	31		Jul	4	4	4	4	5	5	5	31
28		18-08-22		Aug	5	5	5	4	4	4	4	31		Aug	3	4	4	3	3	3	3	23
29		19-08-22		Sep	4	4	4	5	5	4	4	30		Sep	4	4	4	5	5	4	4	30
30		20-08-22		Oct	5	4	4	4	4	5	5	31		Oct	5	4	4	4	4	5	5	31
31		21-08-22		Nov	4	5	5	4	4	4	4	30		Nov	4	5	5	4	4	4	4	30
32		22-08-22		Dec	4	4	4	5	5	5	4	31		Dec	3	3	3	4	4	3	3	23
33		24-12-22		GT	52	52	52	52	52	53	52	365		GT	48	50	50	50	50	49	49	346
34		25-12-22
DNY 5

Cell Formulas
Range	Formula
D2,X23,X21,N19,D19,X6,X4,N2	D2	=FORMULATEXT(D3)
D3:L16	D3	=PTEHDNY(2021)
N3:V16	N3	=PTEHDNY(2021,B3:B40)
X5	X5	=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2021))
X7,X24	X7	=L16-V16
D20:L33	D20	=PTEHDNY(2022)
N20:V33	N20	=PTEHDNY(2022,B3:B40)
X22	X22	=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2022))
Dynamic array formulas.

 

[Xlambda]

What if we have an array of dates (more years) and we want to extract the distribution of weekdays for each month (all years together) ?
Concept:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		random dates btw 1-1-21,31-12-22 300 rows				wkdy distribution entire array
2		=RANDARRAY(300,,"1-1-21","31-12-22",1)				=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(D5#,SEQUENCE(7)),-1))
3		↓↓↓		wkdys entire array		↓↓↓			wkdy distribution only Jan
4		↓↓↓		=WEEKDAY(B5#)		↓↓↓			=FILTER(B5#,MONTH(B5#)=1)
5		11-09-22		1		↓↓↓			↓↓↓		=DROP(FREQUENCY(WEEKDAY(I6#),SEQUENCE(7)),-1)
6		22-10-22		7		Sun	43		21-01-22		7
7		23-07-22		7		Mon	38		31-01-21		1
8		10-08-22		4		Tue	44		27-01-21		2
9		22-12-21		4		Wed	49		23-01-21		3
10		18-07-22		2		Thu	38		26-01-21		5
11		11-04-21		1		Fri	37		12-01-22		3
12		20-08-21		6		Sat	51		21-01-22		7
13		13-06-21		1					04-01-21
14		11-09-22		1					03-01-21
15		12-11-21		6					14-01-22
16		13-09-21		2					14-01-21
17		03-08-22		4					09-01-21
18		20-11-21		7					16-01-21
19		18-04-22		2					20-01-22
20		08-04-22		6					13-01-22
21		04-08-21		4					14-01-21
22		11-03-21		5					02-01-22
23		20-03-21		7					23-01-22
24		20-08-21		6					01-01-22
25		05-12-21		1					26-01-22
26		09-08-22		3					25-01-22
27		21-01-22		6					27-01-22
28		05-06-21		7					22-01-22
29		24-07-21		7					08-01-22
30		17-06-21		5	down to 300 rows				16-01-22
31		03-03-21		4	↓↓↓↓↓↓↓↓↓				15-01-22
32		23-10-21		7					09-01-22
33		22-03-22		3					10-01-21
34		09-04-22		7
Sheet7

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(B5)
F2	F2	=FORMULATEXT(F6)
D4,K5	D4	=FORMULATEXT(D5)
I4	I4	=FORMULATEXT(I6)
B5:B304	B5	=RANDARRAY(300,,"1-1-21","31-12-22",1)
D5:D304	D5	=WEEKDAY(B5#)
F6:G12	F6	=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(D5#,SEQUENCE(7)),-1))
I6:I33	I6	=FILTER(B5#,MONTH(B5#)=1)
K6:K12	K6	=DROP(FREQUENCY(WEEKDAY(I6#),SEQUENCE(7)),-1)
Dynamic array formulas.

 

[Xlambda]

We have seen how we can calculate the distribution of weekdays for 1 month.
So we can use the custom-made lambda helper function that can spill byrow, SPILLBYROW (this thread post#50), a function that can spill rows even if source array is 1D, this format:
SPILLBYROW(SEQUENCE(12),LAMBDA(i,fn(i)))
Here are 2 methods, 2 simple lambda formulas, 2 different ways to solve the inner array (distribution values w/o the rest of other PT elements)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		random dates btw 1-1-21,31-12-22 300 rows
2		=RANDARRAY(300,,"1-1-21","31-12-22",1)
3		↓↓↓
4		↓↓↓		Sun	Mon	Tue	Wed	Thu	Fri	Sat
5		07-09-22		1st method
6		02-11-22		=SPILLBYROW(SEQUENCE(12),LAMBDA(i,TOROW(DROP(FREQUENCY(WEEKDAY(FILTER(B5#,MONTH(B5#)=i)),SEQUENCE(7)),-1))))
7		08-02-21		6	3	3	5	1	8	5		Jan
8		29-07-22		6	3	5	3	2	2	3		Feb
9		06-11-21		2	2	4	4	1	2	1		Mar
10		26-06-21		3	3	3	5	4	1	3		Apr
11		31-05-21		6	5	4	5	2	3	1		May
12		17-05-21		4	1	4	4	6	3	4		Jun
13		20-06-22		5	4	5	3	6	5	3		Jul
14		26-07-21		2	4	5	4	3	3	2		Aug
15		14-10-22		2	3	6	6	4	5	5		Sep
16		02-06-21		2	3	8	1	3	4	4		Oct
17		26-11-21		2	4	3	5	4	3	5		Nov
18		02-01-21		5	3	5	1	2	2	1		Dec
19		31-08-21
20		18-10-21		2nd method
21		04-07-21		=SPILLBYROW(SEQUENCE(12),LAMBDA(i,BYCOL((MONTH(B5#)=i)*(WEEKDAY(B5#)=SEQUENCE(,7)),LAMBDA(x,SUM(x)))))
22		08-05-22		6	3	3	5	1	8	5		Jan
23		23-03-22		6	3	5	3	2	2	3		Feb
24		12-05-22		2	2	4	4	1	2	1		Mar
25		12-12-22		3	3	3	5	4	1	3		Apr
26		26-09-21		6	5	4	5	2	3	1		May
27		08-09-22		4	1	4	4	6	3	4		Jun
28		07-10-21		5	4	5	3	6	5	3		Jul
29		07-01-22		2	4	5	4	3	3	2		Aug
30		23-07-21		2	3	6	6	4	5	5		Sep
31		29-07-21		2	3	8	1	3	4	4		Oct
32		24-12-22		2	4	3	5	4	3	5		Nov
33		01-08-22		5	3	5	1	2	2	1		Dec
34		02-01-22
35		27-09-22
36		18-02-21		checking 1st method
37		03-02-21		=BYCOL(D7#,LAMBDA(x,SUM(x)))
38		29-06-21		45	38	55	46	38	41	37
39		18-05-22
40		14-07-21		checking 2nd method
41		06-01-21		=BYCOL(D22#,LAMBDA(x,SUM(x)))
42		06-02-21		45	38	55	46	38	41	37
43		13-07-22
44		29-03-21		cheking wkdy distribution entire array
45		01-10-22		=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(WEEKDAY(B5#),SEQUENCE(7)),-1))
46		22-09-21		Sun	45
47		08-01-22		Mon	38
48		30-11-21		Tue	55
49		17-01-21		Wed	46
50		03-11-21		Thu	38
51		06-12-22		Fri	41
52		22-05-22		Sat	37
53		02-01-22
54		05-10-21
55		02-12-22	down to 300 rows
56		11-06-22	↓↓↓↓↓↓↓↓↓
57		31-10-22
Sheet7

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(B5)
D4:J4	D4	=TEXT(SEQUENCE(,7),"ddd")
B5:B304	B5	=RANDARRAY(300,,"1-1-21","31-12-22",1)
D6,D45,D41,D37,D21	D6	=FORMULATEXT(D7)
D7:J18	D7	=SPILLBYROW(SEQUENCE(12),LAMBDA(i,TOROW(DROP(FREQUENCY(WEEKDAY(FILTER(B5#,MONTH(B5#)=i)),SEQUENCE(7)),-1))))
D22:J33	D22	=SPILLBYROW(SEQUENCE(12),LAMBDA(i,BYCOL((MONTH(B5#)=i)*(WEEKDAY(B5#)=SEQUENCE(,7)),LAMBDA(x,SUM(x)))))
D38:J38	D38	=BYCOL(D7#,LAMBDA(x,SUM(x)))
D42:J42	D42	=BYCOL(D22#,LAMBDA(x,SUM(x)))
D46:E52	D46	=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(WEEKDAY(B5#),SEQUENCE(7)),-1))
Dynamic array formulas.

 

[Xlambda]

Task: Using a lambda formula (SPILLBYROW versatility) extract all weekdays dates from an array of dates.

Nr weekdays 2481.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		random dates btw 1-1-21,31-12-22 50 rows
2		=RANDARRAY(50,,"1-1-21","31-12-22",1)
3		↓↓↓
4		↓↓↓		=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(FILTER(a,WEEKDAY(a)=i)))))))
5		20-03-21		Sun	13-06-21	01-08-21	23-01-22	31-07-22	25-09-22	25-12-22
6		23-04-21		Mon	10-05-21	04-04-22	11-07-22	08-08-22	12-09-22
7		27-07-21		Tue	27-07-21	07-12-21	30-08-22	27-09-22
8		07-12-21		Wed	13-01-21	05-05-21	07-07-21	13-10-21	02-03-22	04-05-22	24-08-22	14-09-22	14-09-22	30-11-22
9		27-09-22		Thu	18-03-21	22-04-21	06-05-21	25-11-21	02-12-21	13-01-22	10-02-22	09-06-22	29-12-22
10		01-01-22		Fri	19-03-21	23-04-21	01-10-21	29-04-22	20-05-22	10-06-22	29-07-22
11		20-05-22		Sat	02-01-21	20-03-21	04-09-21	18-09-21	25-12-21	01-01-22	22-01-22	22-10-22	05-11-22
12		05-11-22
13		19-03-21			check
14		30-08-22			=IFERROR(WEEKDAY(DROP(D5#,,1)),"")
15		31-07-22			1	1	1	1	1	1
16		09-06-22			2	2	2	2	2
17		25-12-21			3	3	3	3
18		08-08-22			4	4	4	4	4	4	4	4	4	4
19		01-10-21			5	5	5	5	5	5	5	5	5
20		22-01-22			6	6	6	6	6	6	6
21		25-12-22			7	7	7	7	7	7	7	7	7
22		29-12-22
23		22-10-22
24		12-09-22
25		13-01-21
26		23-01-22
27		04-04-22
28		29-07-22
29		10-02-22
30		10-05-21
31		24-08-22
32		13-06-21
33		04-09-21
34		10-06-22
35		11-07-22
36		14-09-22
37		25-11-21
38		18-09-21
39		29-04-22
40		01-08-21
41		02-03-22
42		14-09-22
43		13-10-21
44		05-05-21
45		04-05-22
46		02-12-21
47		07-07-21
48		06-05-21
49		22-04-21
50		25-09-22
51		02-01-21
52		13-01-22
53		18-03-21
54		30-11-22
55
DNY 7

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(B5)
D4,E14	D4	=FORMULATEXT(D5)
B5:B54	B5	=RANDARRAY(50,,"1-1-21","31-12-22",1)
D5:N11	D5	=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(FILTER(a,WEEKDAY(a)=i)))))))
E15:N21	E15	=IFERROR(WEEKDAY(DROP(D5#,,1)),"")
Dynamic array formulas.

 

[Xlambda]

Forgot to add UNIQUE, in case dates array has dups.
.....SPILLBYROW(s,LAMBDA(i,TOROW(SORT(UNIQUE(FILTER(a,WEEKDAY(a)=i))))))))

Nr weekdays 2481.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		random dates btw 1-1-21,31-12-22 50 rows
2		=RANDARRAY(50,,"1-1-21","31-12-22",1)
3		↓↓↓
4		↓↓↓		=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(UNIQUE(FILTER(a,WEEKDAY(a)=i))))))))
5		18-04-22		Sun	11-04-21	24-10-21	09-01-22	13-03-22	26-06-22	04-12-22
6		11-04-21		Mon	05-07-21	19-07-21	29-11-21	27-12-21	03-01-22	11-04-22	18-04-22	19-09-22
7		07-01-21		Tue	26-01-21	06-07-21	19-10-21	07-12-21	08-02-22	18-10-22	29-11-22
8		01-09-22		Wed	10-02-21	10-03-21	02-06-21	22-09-21	08-12-21	02-02-22	16-02-22	09-03-22	18-05-22	03-08-22
9		03-06-21		Thu	07-01-21	25-02-21	03-06-21	11-11-21	03-02-22	24-02-22	07-04-22	23-06-22	01-09-22	29-09-22
10		03-09-22		Fri	15-01-21	21-01-22	01-07-22
11		03-02-22		Sat	04-06-22	03-09-22	22-10-22	10-12-22	31-12-22
12		29-11-21
13		21-01-22			check
14		08-12-21			=IFERROR(WEEKDAY(DROP(D5#,,1)),"")
15		26-06-22			1	1	1	1	1	1
16		10-12-22			2	2	2	2	2	2	2	2
17		29-11-22			3	3	3	3	3	3	3
18		03-08-22			4	4	4	4	4	4	4	4	4	4
19		19-09-22			5	5	5	5	5	5	5	5	5	5
20		08-02-22			6	6	6
21		18-05-22			7	7	7	7	7
22		13-03-22
23		04-06-22
24		02-06-21
25		27-12-21
26		05-07-21
27		23-06-22
28		25-02-21
29		22-09-21
30		24-10-21
31		06-07-21
32		01-07-22
33		19-07-21
34		18-10-22
35		09-01-22
36		10-02-21
37		29-09-22
38		26-01-21
39		09-03-22
40		25-02-21
41		15-01-21
42		03-01-22
43		10-03-21
44		16-02-22
45		22-10-22
46		04-12-22
47		11-04-22
48		24-02-22
49		07-12-21
50		11-11-21
51		02-02-22
52		07-04-22
53		31-12-22
54		19-10-21
55
DNY 7

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(B5)
D4,E14	D4	=FORMULATEXT(D5)
B5:B54	B5	=RANDARRAY(50,,"1-1-21","31-12-22",1)
D5:N11	D5	=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(UNIQUE(FILTER(a,WEEKDAY(a)=i))))))))
E15:N21	E15	=IFERROR(WEEKDAY(DROP(D5#,,1)),"")
Dynamic array formulas.