ACOUNT

[Xlambda]

ACOUNT Array Count, or self-count, replaces functionality of: COUNTIF(S)(range,range), COUNTIF(S) with expandable ranges for occurrence counting and COUNTIF(S)(range,unique(range)) for unique total counts. Calls AFLAT, ARESIZE. Uses NEW!! MAP, MAKEARRAY

=LAMBDA(a,[ct],[pu],
    LET(r,ROWS(a),f,AFLAT(a,1),w,ROWS(f),q,UNIQUE(AFLAT(a)),u,IF(ct=2,q,f),
       p,MAP(u,LAMBDA(x,SUM(--(x=f)))),k,MAKEARRAY(w,,LAMBDA(r,i,SUM(--(INDEX(f,SEQUENCE(r))=INDEX(f,r))))),
       y,SWITCH(ct,0,ARESIZE(p,r),1,ARESIZE(k,r),2,IF(pu,CHOOSE({1,2},q,p),p)),
       IF(ISNA(y),"check arg.",IFERROR(IF(ct=2,y,IF(a="","",y)),""))
    )
)

Will start with the basic lambda "formulas" structure. This concept follows the conclusion we got from last post of ASCAN thread, and took shape since the AMORTIZE thread, which has several lambda "formulas" combinations of new helper functions, in various constructions.
For the ones who did not view ASCAN thread, general idea was: These new lambda helper functions are so intrinsic versatile and powerful that, (I think), their main purpose is not to help us in lambda constructions, quite the opposite, they were created to help us in lambdas "deconstruction".
Applied to this very case, we have a lambda function that calls 2 other lambdas, lot of cosmetics, but the core of the function consists in 2 quite simple lambda "formulas" that can be used explicitly every time we need them, without calling the "whole" lambda. Mastering lambda formulas becomes more importantant than whatever lambada function we decide to design.

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1. self count 1D array				since countif(s) can not support array calculations
2			good old countif(a,a)		because of range argument, a lambda formula can replace it:
3	aa		=COUNTIF(aa,aa)		=MAP(aa,LAMBDA(x,SUM(--(x=aa))))
4	a		3		3
5	a		3		3
6	d		2		2
7	d		2		2
8	a		3		3
9	c		2		2
10	b		1		1
11	c		2		2
12
13	2. self occurrence count 1D arrays
14			countif expandable range		lambda formula with embedded expandable range functionality
15	=aa		=COUNTIF($A$16:A16,A16)		=MAKEARRAY(8,,LAMBDA(r,i,SUM(--(INDEX(aa,SEQUENCE(r))=INDEX(aa,r)))))
16	a		1		1
17	a		2		2
18	d		1		1
19	d		2		2
20	a		3		3
21	c		1		1
22	b		1		1
23	c		2		2
24
25	3. unique total count
26					lambda formula
27	=UNIQUE(aa)		=COUNTIF(aa,UNIQUE(aa))		=MAP(UNIQUE(aa),LAMBDA(x,SUM(--(x=aa))))
28	a		3		3
29	d		2		2
30	c		2		2
31	b		1		1
32
ACOUNT post 1

Cell Formulas
Range	Formula
C3,E3,A27,C27,E27,A15,C15,E15	C3	=FORMULATEXT(C4)
C4:C11	C4	=COUNTIF(aa,aa)
E4:E11	E4	=MAP(aa,LAMBDA(x,SUM(--(x=aa))))
A16:A23	A16	=aa
E16:E23	E16	=MAKEARRAY(8,,LAMBDA(r,i,SUM(--(INDEX(aa,SEQUENCE(r))=INDEX(aa,r)))))
C16:C23	C16	=COUNTIF($A$16:A16,A16)
A28:A31	A28	=UNIQUE(aa)
C28:C31	C28	=COUNTIF(aa,UNIQUE(aa))
E28:E31	E28	=MAP(UNIQUE(aa),LAMBDA(x,SUM(--(x=aa))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
aa	='ACOUNT post 1'!$A$4:$A$11	E28, E16, E4, C28, C4, A28, A16

 

[Xlambda]

LAMBDA 1.1.4.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
1	4. self count + handling blanks/null strings 1D horiz. array
2								ab		=COUNTIF(ab,ab)
3	a			b	a	a	b			3	3	0	2	3	3	2	0
4										null strings counted together with blanks
5		null string B3:=""								blanks not counted
6		blank
7										=MAP(ab,LAMBDA(x,SUM(--(x=ab))))
8										3	3	3	2	3	3	2	3
9										lambda formula, blanks<=>null strings,counted toghether
10
11										to discard blanks/null strings
12										=IF(ab="","",J8#)
13										3			2	3	3	2
14
15	5. self occurrence count									countif expandable range
16										=COUNTIF($A$17:A17,A17)
17	a			b	a	a	b			1	1	0	1	2	3	2	0
18
19										=MAKEARRAY(,8,LAMBDA(i,c,SUM(--(INDEX(ab,SEQUENCE(c))=INDEX(ab,c)))))
20										1	1	2	1	2	3	2	3
21										lambda formula, blanks<=>null strings,counted toghether
22
23										to discard blanks/null strings
24										=IF(ab="","",J20#)
25										1			1	2	3	2
26
27	6. unique total count
28
29	=UNIQUE(ab,1)									=COUNTIF(ab,UNIQUE(ab,1))
30	a		0	b						3	3	0	2
31
32	we have to use filter									=COUNTIF(ab,UNIQUE(FILTER(ab,ab<>""),1))
33	=UNIQUE(FILTER(ab,ab<>""),1)									3	2
34	a	b
35										lambda formula
36										=MAP(UNIQUE(FILTER(ab,ab<>""),1),LAMBDA(x,SUM(--(x=ab))))
37										3	2
38
ACOUNT post 2

Cell Formulas
Range	Formula
J2,J36,A33,J32,J29,A29,J24,J19,J16,J12,J7	J2	=FORMULATEXT(J3)
B3,B17	B3	=""
J3:Q3	J3	=COUNTIF(ab,ab)
J8:Q8	J8	=MAP(ab,LAMBDA(x,SUM(--(x=ab))))
J13:Q13,J25:Q25	J13	=IF(ab="","",J8#)
J17:Q17	J17	=COUNTIF($A$17:A17,A17)
J20:Q20	J20	=MAKEARRAY(,8,LAMBDA(i,c,SUM(--(INDEX(ab,SEQUENCE(c))=INDEX(ab,c)))))
A30:D30	A30	=UNIQUE(ab,1)
J30:M30	J30	=COUNTIF(ab,UNIQUE(ab,1))
J33:K33	J33	=COUNTIF(ab,UNIQUE(FILTER(ab,ab<>""),1))
A34:B34	A34	=UNIQUE(FILTER(ab,ab<>""),1)
J37:K37	J37	=MAP(UNIQUE(FILTER(ab,ab<>""),1),LAMBDA(x,SUM(--(x=ab))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ab	='ACOUNT post 2'!$A$3:$H$3	J37, J33, J30, J25, J20, J13, J8, J3, A34, A30

 

[Xlambda]

LAMBDA 1.1.4.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
1	7. self count 2D array														9. unique total count 2D array
2					ac		=COUNTIF(ac,ac)
3	a	a		b	c		5	5	0	2	3				2 ways
4	c		b		a		3	4	2	4	5				=UNIQUE(AFLAT(ac))				=AFUSBYROW(ac,,2)
5	d	c		a	a		1	3	0	5	5				a				a
6															b				b
7		null string B4,D4 =""					lambda formula								c				c
8		blank					=MAP(ac,LAMBDA(x,SUM(--(x=ac))))								d				d
9							5	5	4	2	3
10							3	4	2	4	5				=COUNTIF(ac,O5#)
11							1	3	4	5	5				5
12															2		lambda formula
13							=IF(ac="","",G9#)								3		=MAP(S5#,LAMBDA(x,SUM(--(x=ac))))
14							5	5		2	3				1		5
15							3		2		5						2
16							1	3		5	5						3
17																	1
18	8. self occurrence count 2D arrays
19	since there is no 2D expandable range
20	will use AFLAT(a,1) (blnks./null strings kept as null strings)
21	=AFLAT(ac,1)
22	a			=COUNTIF($A$22:A22,A22)
23	a			1
24				2		lambda formula
25	b			1		=MAKEARRAY(15,,LAMBDA(r,i,LET(f,AFLAT(ac,1),SUM(--(INDEX(f,SEQUENCE(r))=INDEX(f,r))))))
26	c			1		1
27	c			1		2
28				2		1			to get to initial array shape, will use ARESIZE
29	b			2		1			=ARESIZE(F26#,3)						=ARESIZE(F26#,,5)
30				2		1			1	2	1	1	1	or	1	2	1	1	1
31	a			3		2			2	2	2	3	3		2	2	2	3	3
32	d			3		2			1	3	4	4	5		1	3	4	4	5
33	c			1		2
34				3		3			=IF(ac="","",I30#)
35	a			4		3			1	2		1	1			other functions
36	a			4		1			2		2		3			AFLAT
37				5		3			1	3		4	5			ARESIZE
38						4										AFUSBYROW
39						4
40						5
41
ACOUNT post 3

Cell Formulas
Range	Formula
G2,I34,O29,I29,F25,D22,A21,Q13,G13,O10,G8,O4,S4	G2	=FORMULATEXT(G3)
G3:K5	G3	=COUNTIF(ac,ac)
B4,D4	B4	=""
O5:O8	O5	=UNIQUE(AFLAT(ac))
S5:S8	S5	=AFUSBYROW(ac,,2)
G9:K11	G9	=MAP(ac,LAMBDA(x,SUM(--(x=ac))))
O11:O14	O11	=COUNTIF(ac,O5#)
G14:K16,I35:M37	G14	=IF(ac="","",G9#)
Q14:Q17	Q14	=MAP(S5#,LAMBDA(x,SUM(--(x=ac))))
A22:A36	A22	=AFLAT(ac,1)
F26:F40	F26	=MAKEARRAY(15,,LAMBDA(r,i,LET(f,AFLAT(ac,1),SUM(--(INDEX(f,SEQUENCE(r))=INDEX(f,r))))))
I30:M32	I30	=ARESIZE(F26#,3)
O30:S32	O30	=ARESIZE(F26#,,5)
D23:D37	D23	=COUNTIF($A$22:A22,A22)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ac	='ACOUNT post 3'!$A$3:$E$5	S5, Q14, O11, O5, I35, G14, G9, G3, F26, A22

 

[Xlambda]

The function.
ACOUNT is only one of the multiple ways we can assemble these lambda formulas.
We are only scratching the surface of what lambda formulas can do to replace all …IFS functions and their range arguments. Lots of "formulas" in different functions will follow.
If someone is not happy with a function design, or wants to add, or remove stuff, or use their own lambda library, or even redesign it completely, I think is much easier now, if there is a good, simple, and robust formula structure.

LAMBDA 1.1.4.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
1	The function
2							ct,pu,omitted (self count)						ct,1,pu,omitted (self occurrence count)							checking null strings output
3	sample 1						=ACOUNT(A4:E6)						=ACOUNT(A4:E6,1)							=M4#=""
4	a	a		b	c		5	5		2	3		1	2		1	1			FALSE	FALSE	TRUE	FALSE	FALSE
5	c		b	#N/A	a		3		2		5		2		2		3			FALSE	TRUE	FALSE	TRUE	FALSE
6	d	c		a	a		1	3		5	5		1	3		4	5			FALSE	FALSE	TRUE	FALSE	FALSE
7
8							ct,2,pu,omitted (unique count)						ct,2,pu,1 (array {unique values,unique count)
9		null string					=ACOUNT(A4:E6,2)						=ACOUNT(A4:E6,2,1)
10		blank					5						a	5
11		error					2						b	2
12							3						c	3
13							1						d	1
14
15							pu has effect only when ct=2						wrong arguments
16							ct,1,pu,1						ct,3
17							=ACOUNT(A4:E6,1,1)						=ACOUNT(A4:E6,3)
18							1	2		1	1		check arg.
19							2		2		3
20							1	3		4	5
21
22										ct,pu,omitted (self count)									ct,2,pu,0 (unique count)
23	sample 2									=ACOUNT(A24:H24)									=ACOUNT(A24:H24,2,0)
24	a			b	a	a	b			3			2	3	3	2			3
25																			2		ct,2,pu,3 (pu<>0) (array)
26										ct,1,pu,omitted (self occurrence count)											=ACOUNT(A24:H24,2,3)
27										=ACOUNT(A24:H24,1)											a	3
28										1			1	2	3	2					b	2
29
30
31				ct,pu,omitted (self count)					ct,1,pu,omitted (self occurrence count)
32	sample 3			=ACOUNT(A33:A40)					=ACOUNT(A33:A40,1)
33	a			3					1				ct,2,pu,omitted (unique count)
34	a			3					2				=ACOUNT(A33:A40,2)
35	d			2					1				3
36	d			2					2				2			ct,2,pu,1 (array {unique values,unique count)
37	a			3					3				2			=ACOUNT(A33:A40,2,1)
38	c			2					1				1			a	3
39	b			1					1							d	2
40	c			2					2							c	2
41																b	1
42
ACOUNT post 4

Cell Formulas
Range	Formula
G3,P37,M34,I32,D32,J27,U26,S23,J23,M17,G17,M9,G9,T3,M3	G3	=FORMULATEXT(G4)
G4:K6	G4	=ACOUNT(A4:E6)
M4:Q6	M4	=ACOUNT(A4:E6,1)
T4:X6	T4	=M4#=""
B5,B24	B5	=""
D5	D5	=NA()
G10:G13	G10	=ACOUNT(A4:E6,2)
M10:N13	M10	=ACOUNT(A4:E6,2,1)
G18:K20	G18	=ACOUNT(A4:E6,1,1)
M18	M18	=ACOUNT(A4:E6,3)
J24:Q24	J24	=ACOUNT(A24:H24)
S24:S25	S24	=ACOUNT(A24:H24,2,0)
U27:V28	U27	=ACOUNT(A24:H24,2,3)
J28:Q28	J28	=ACOUNT(A24:H24,1)
D33:D40	D33	=ACOUNT(A33:A40)
I33:I40	I33	=ACOUNT(A33:A40,1)
M35:M38	M35	=ACOUNT(A33:A40,2)
P38:Q41	P38	=ACOUNT(A33:A40,2,1)
Dynamic array formulas.

 

[Xlambda]

Couple of days ago I saw latest YT of Mynda's MyOnlineTrainingHub:
Dynamic Power Query Cross Tab Reports - Pivot n Rows to n Columns 2 Ways!
I thought it's a cool example for ACOUNT. Here is the single cell formula I came out with: (a,1st clm ; b,2nd clm ; h,headers ) (calls AREPORT , ACOUNT)

=LAMBDA(a,b,h,
    LET(u,UNIQUE(a),q,XMATCH(a,u),m,ACOUNT(q,1),c,MAX(m),
      x,XLOOKUP(SEQUENCE(ROWS(u),c),c*(q-1)+m,b,""),
     AREPORT(x,u,"Column"&SEQUENCE(,c),,,h)
    )
)

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	The idea of the video was to get
2	from this:		to	this:
3	Location	Department		Location	Column1	Column2	Column3	Column4	Column5
4	Brisbane	Finance		Brisbane	Finance	I.T.	Logistics
5	Brisbane	I.T.		Sydney	Finance
6	Brisbane	Logistics		Dublin	Finance	I.T.	Logistics	HR
7	Sydney	Finance		Copenhagen	I.T.	Logistics	HR	Marketing	Design
8	Dublin	Finance		London	HR	Marketing	Finance				main trick of whole formula
9	Dublin	I.T.									ACOUNT does occurrence count of (q)				C*(q-1)+m
10	Dublin	Logistics		1. calculating inner array rows nr. (R)				2. rows sequence (q)			3. clms sequeq.(m)+clms nr.(C)				4. array sequence
11	Dublin	HR		=UNIQUE(A4:A19)		=ROWS(D12#)		=XMATCH(A4:A19,D12#)			=ACOUNT(H12#,1)		=MAX(K12#)		=5*(H12#-1)+K12#
12	Copenhagen	I.T.		Brisbane		5		1	(q)		1	(m)	5		1
13	Copenhagen	Logistics		Sydney		(R)		1			2		(C)		2
14	Copenhagen	HR		Dublin				1			3				3
15	Copenhagen	Marketing		Copenhagen				2			1				6
16	Copenhagen	Design		London				3			1				11
17	London	HR						3			2				12
18	London	Marketing						3			3				13
19	London	Finance						3			4				14
20								4			1				16
21								4			2				17
22								4			3				18
23								4			4				19
24	other functions							4			5				20
25	AREPORT							5			1				21
26								5			2				22
27								5			3				23
28					5.inner array-xlookup						6. cosmetics-final report
29					=XLOOKUP(SEQUENCE(5,5),O12#,B4:B19,"")						=AREPORT(E30#,D12#,"Column"&SEQUENCE(,5),,,"Deparment")
30					Finance	I.T.	Logistics				Deparment	Column1	Column2	Column3	Column4	Column5
31					Finance						Brisbane	Finance	I.T.	Logistics
32					Finance	I.T.	Logistics	HR			Sydney	Finance
33					I.T.	Logistics	HR	Marketing	Design		Dublin	Finance	I.T.	Logistics	HR
34					HR	Marketing	Finance				Copenhagen	I.T.	Logistics	HR	Marketing	Design
35											London	HR	Marketing	Finance
36				single cell
37				Location	Column1	Column2	Column3	Column4	Column5
38				Brisbane	Finance	I.T.	Logistics
39				Sydney	Finance
40				Dublin	Finance	I.T.	Logistics	HR
41				Copenhagen	I.T.	Logistics	HR	Marketing	Design
42				London	HR	Marketing	Finance
43
44	=LAMBDA(a,b,h,LET(u,UNIQUE(a),q,XMATCH(a,u),m,ACOUNT(q,1),c,MAX(m),x,XLOOKUP(SEQUENCE(ROWS(u),c),c*(q-1)+m,b,""),AREPORT(x,u,"Column"&SEQUENCE(,c),,,h)))(A4:A19,B4:B19,A3)
45
ACOUNT post 5

Cell Formulas
Range	Formula
D11,F11,H11,K11,M11,O11,K29,E29	D11	=FORMULATEXT(D12)
D12:D16	D12	=UNIQUE(A4:A19)
F12	F12	=ROWS(D12#)
H12:H27	H12	=XMATCH(A4:A19,D12#)
K12:K27	K12	=ACOUNT(H12#,1)
M12	M12	=MAX(K12#)
O12:O27	O12	=5*(H12#-1)+K12#
E30:I34	E30	=XLOOKUP(SEQUENCE(5,5),O12#,B4:B19,"")
K30:P35	K30	=AREPORT(E30#,D12#,"Column"&SEQUENCE(,5),,,"Deparment")
D37:I42	D37	=LAMBDA(a,b,h,LET(u,UNIQUE(a),q,XMATCH(a,u),m,ACOUNT(q,1),c,MAX(m),x,XLOOKUP(SEQUENCE(ROWS(u),c),c*(q-1)+m,b,""),AREPORT(x,u,"Column"&SEQUENCE(,c),,,h)))(A4:A19,B4:B19,A3)
A44	A44	=FORMULATEXT(D37)
Dynamic array formulas.

 

[Xlambda]

Different output that should be considered when unpivoting. 2 ways with XMATCH and MAP. These work without ACOUNT.

=LAMBDA(a,b,h,
    LET(u,UNIQUE(a),t,UNIQUE(b),q,XMATCH(a,u),m,XMATCH(b,t),c,MAX(m),x,
       IF(XMATCH(SEQUENCE(ROWS(u),c),c*(q-1)+m),"x",""),
      AREPORT(x,u,TRANSPOSE(t),,,h)
    )
)

=LAMBDA(a,b,h,
    LET(u,UNIQUE(a),t,UNIQUE(b),q,XMATCH(a,u),r,ROWS(u),m,XMATCH(b,t),c,MAX(m),s,c*(q-1)+m,x,
        MAP(SEQUENCE(r,c),LAMBDA(a,IF(OR(a=s),"x",""))),
        AREPORT(x,u,TRANSPOSE(t),,,h)
    )
)

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Different output, 2 ways
2				1. xmatch sol.
3	Location	Department		Location	Finance	I.T.	Logistics	HR	Marketing	Design
4	Brisbane	Finance		Brisbane	x	x	x
5	Brisbane	I.T.		Sydney	x
6	Brisbane	Logistics		Dublin	x	x	x	x
7	Sydney	Finance		Copenhagen		x	x	x	x	x
8	Dublin	Finance		London	x			x	x
9	Dublin	I.T.
10	Dublin	Logistics		2. map sol.
11	Dublin	HR		Location	Finance	I.T.	Logistics	HR	Marketing	Design
12	Copenhagen	I.T.		Brisbane	x	x	x
13	Copenhagen	Logistics		Sydney	x
14	Copenhagen	HR		Dublin	x	x	x	x
15	Copenhagen	Marketing		Copenhagen		x	x	x	x	x
16	Copenhagen	Design		London	x			x	x
17	London	HR
18	London	Marketing		other functions
19	London	Finance		AREPORT
20
ACOUNT post 6

Cell Formulas
Range	Formula
D3:J8	D3	=LAMBDA(a,b,h,LET(u,UNIQUE(a),t,UNIQUE(b),q,XMATCH(a,u),m,XMATCH(b,t),c,MAX(m),x,IF(XMATCH(SEQUENCE(ROWS(u),c),c*(q-1)+m),"x",""),AREPORT(x,u,TRANSPOSE(t),,,h)))(A4:A19,B4:B19,A3)
D11:J16	D11	=LAMBDA(a,b,h,LET(u,UNIQUE(a),t,UNIQUE(b),q,XMATCH(a,u),r,ROWS(u),m,XMATCH(b,t),c,MAX(m),s,c*(q-1)+m,x,MAP(SEQUENCE(r,c),LAMBDA(a,IF(OR(a=s),"x",""))),AREPORT(x,u,TRANSPOSE(t),,,h)))(A4:A19,B4:B19,A3)
Dynamic array formulas.