ACOUNT

ACOUNT(a,[ct],[pu])
a
array
[ct]
count type argument: 0 or omitted, self-count ; 1, occurrence self-count ; 2, unique count
[pu]
print unique argument: 0 or omitted, unique count ; 1 or <>0, array {unique values, unique count}
Study, array self-count/occurrence self-count/unique count , NEW!! MAP , MAKEARRAY
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRST
11. self count 1D arraysince countif(s) can not support array calculations
2good old countif(a,a)because of range argument, a lambda formula can replace it:
3aa=COUNTIF(aa,aa)=MAP(aa,LAMBDA(x,SUM(--(x=aa))))
4a33
5a33
6d22
7d22
8a33
9c22
10b11
11c22
12
132. self occurrence count 1D arrays
14countif expandable rangelambda 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)))))
16a11
17a22
18d11
19d22
20a33
21c11
22b11
23c22
24
253. unique total count
26lambda formula
27=UNIQUE(aa)=COUNTIF(aa,UNIQUE(aa))=MAP(UNIQUE(aa),LAMBDA(x,SUM(--(x=aa))))
28a33
29d22
30c22
31b11
32
ACOUNT post 1
Cell Formulas
RangeFormula
C3,E3,A27,C27,E27,A15,C15,E15C3=FORMULATEXT(C4)
C4:C11C4=COUNTIF(aa,aa)
E4:E11E4=MAP(aa,LAMBDA(x,SUM(--(x=aa))))
A16:A23A16=aa
E16:E23E16=MAKEARRAY(8,,LAMBDA(r,i,SUM(--(INDEX(aa,SEQUENCE(r))=INDEX(aa,r)))))
C16:C23C16=COUNTIF($A$16:A16,A16)
A28:A31A28=UNIQUE(aa)
C28:C31C28=COUNTIF(aa,UNIQUE(aa))
E28:E31E28=MAP(UNIQUE(aa),LAMBDA(x,SUM(--(x=aa))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
aa='ACOUNT post 1'!$A$4:$A$11E28, E16, E4, C28, C4, A28, A16
 
Last edited:
Upvote 0
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
14. self count + handling blanks/null strings 1D horiz. array
2ab=COUNTIF(ab,ab)
3a baab33023320
4null strings counted together with blanks
5null string B3:=""blanks not counted
6blank
7=MAP(ab,LAMBDA(x,SUM(--(x=ab))))
833323323
9lambda formula, blanks<=>null strings,counted toghether
10
11to discard blanks/null strings
12=IF(ab="","",J8#)
1332332
14
155. self occurrence countcountif expandable range
16=COUNTIF($A$17:A17,A17)
17a baab11012320
18
19=MAKEARRAY(,8,LAMBDA(i,c,SUM(--(INDEX(ab,SEQUENCE(c))=INDEX(ab,c)))))
2011212323
21lambda formula, blanks<=>null strings,counted toghether
22
23to discard blanks/null strings
24=IF(ab="","",J20#)
2511232
26
276. unique total count
28
29=UNIQUE(ab,1)=COUNTIF(ab,UNIQUE(ab,1))
30a0b3302
31
32we have to use filter=COUNTIF(ab,UNIQUE(FILTER(ab,ab<>""),1))
33=UNIQUE(FILTER(ab,ab<>""),1)32
34ab
35lambda formula
36=MAP(UNIQUE(FILTER(ab,ab<>""),1),LAMBDA(x,SUM(--(x=ab))))
3732
38
ACOUNT post 2
Cell Formulas
RangeFormula
J2,J36,A33,J32,J29,A29,J24,J19,J16,J12,J7J2=FORMULATEXT(J3)
B3,B17B3=""
J3:Q3J3=COUNTIF(ab,ab)
J8:Q8J8=MAP(ab,LAMBDA(x,SUM(--(x=ab))))
J13:Q13,J25:Q25J13=IF(ab="","",J8#)
J17:Q17J17=COUNTIF($A$17:A17,A17)
J20:Q20J20=MAKEARRAY(,8,LAMBDA(i,c,SUM(--(INDEX(ab,SEQUENCE(c))=INDEX(ab,c)))))
A30:D30A30=UNIQUE(ab,1)
J30:M30J30=COUNTIF(ab,UNIQUE(ab,1))
J33:K33J33=COUNTIF(ab,UNIQUE(FILTER(ab,ab<>""),1))
A34:B34A34=UNIQUE(FILTER(ab,ab<>""),1)
J37:K37J37=MAP(UNIQUE(FILTER(ab,ab<>""),1),LAMBDA(x,SUM(--(x=ab))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ab='ACOUNT post 2'!$A$3:$H$3J37, J33, J30, J25, J20, J13, J8, J3, A34, A30
 
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
17. self count 2D array9. unique total count 2D array
2ac=COUNTIF(ac,ac)
3aabc550232 ways
4c b a34245=UNIQUE(AFLAT(ac))=AFUSBYROW(ac,,2)
5dcaa13055aa
6bb
7null string B4,D4 =""lambda formulacc
8blank=MAP(ac,LAMBDA(x,SUM(--(x=ac))))dd
955423
1034245=COUNTIF(ac,O5#)
11134555
122lambda formula
13=IF(ac="","",G9#)3=MAP(S5#,LAMBDA(x,SUM(--(x=ac))))
14552315
153252
1613553
171
188. self occurrence count 2D arrays
19since there is no 2D expandable range
20will use AFLAT(a,1) (blnks./null strings kept as null strings)
21=AFLAT(ac,1)
22a=COUNTIF($A$22:A22,A22)
23a1
242lambda formula
25b1=MAKEARRAY(15,,LAMBDA(r,i,LET(f,AFLAT(ac,1),SUM(--(INDEX(f,SEQUENCE(r))=INDEX(f,r))))))
26c11
27c12
2821to get to initial array shape, will use ARESIZE
29b21=ARESIZE(F26#,3)=ARESIZE(F26#,,5)
302112111or12111
31a322223322233
32d321344513445
33c12
3433=IF(ac="","",I30#)
35a431211other functions
36a41223AFLAT
37531345ARESIZE
384AFUSBYROW
394
405
41
ACOUNT post 3
Cell Formulas
RangeFormula
G2,I34,O29,I29,F25,D22,A21,Q13,G13,O10,G8,O4,S4G2=FORMULATEXT(G3)
G3:K5G3=COUNTIF(ac,ac)
B4,D4B4=""
O5:O8O5=UNIQUE(AFLAT(ac))
S5:S8S5=AFUSBYROW(ac,,2)
G9:K11G9=MAP(ac,LAMBDA(x,SUM(--(x=ac))))
O11:O14O11=COUNTIF(ac,O5#)
G14:K16,I35:M37G14=IF(ac="","",G9#)
Q14:Q17Q14=MAP(S5#,LAMBDA(x,SUM(--(x=ac))))
A22:A36A22=AFLAT(ac,1)
F26:F40F26=MAKEARRAY(15,,LAMBDA(r,i,LET(f,AFLAT(ac,1),SUM(--(INDEX(f,SEQUENCE(r))=INDEX(f,r))))))
I30:M32I30=ARESIZE(F26#,3)
O30:S32O30=ARESIZE(F26#,,5)
D23:D37D23=COUNTIF($A$22:A22,A22)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ac='ACOUNT post 3'!$A$3:$E$5S5, Q14, O11, O5, I35, G14, G9, G3, F26, A22
 
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1The function
2ct,pu,omitted (self count)ct,1,pu,omitted (self occurrence count)checking null strings output
3sample 1=ACOUNT(A4:E6)=ACOUNT(A4:E6,1)=M4#=""
4aabc55231211FALSEFALSETRUEFALSEFALSE
5c b#N/Aa325223FALSETRUEFALSETRUEFALSE
6dcaa13551345FALSEFALSETRUEFALSEFALSE
7
8ct,2,pu,omitted (unique count)ct,2,pu,1 (array {unique values,unique count)
9null string=ACOUNT(A4:E6,2)=ACOUNT(A4:E6,2,1)
10blank5a5
11error2b2
123c3
131d1
14
15pu has effect only when ct=2wrong arguments
16ct,1,pu,1ct,3
17=ACOUNT(A4:E6,1,1)=ACOUNT(A4:E6,3)
181211check arg.
19223
201345
21
22ct,pu,omitted (self count)ct,2,pu,0 (unique count)
23sample 2=ACOUNT(A24:H24)=ACOUNT(A24:H24,2,0)
24a baab323323
252ct,2,pu,3 (pu<>0) (array)
26ct,1,pu,omitted (self occurrence count)=ACOUNT(A24:H24,2,3)
27=ACOUNT(A24:H24,1)a3
2811232b2
29
30
31ct,pu,omitted (self count)ct,1,pu,omitted (self occurrence count)
32sample 3=ACOUNT(A33:A40)=ACOUNT(A33:A40,1)
33a31ct,2,pu,omitted (unique count)
34a32=ACOUNT(A33:A40,2)
35d213
36d222ct,2,pu,1 (array {unique values,unique count)
37a332=ACOUNT(A33:A40,2,1)
38c211a3
39b11d2
40c22c2
41b1
42
ACOUNT post 4
Cell Formulas
RangeFormula
G3,P37,M34,I32,D32,J27,U26,S23,J23,M17,G17,M9,G9,T3,M3G3=FORMULATEXT(G4)
G4:K6G4=ACOUNT(A4:E6)
M4:Q6M4=ACOUNT(A4:E6,1)
T4:X6T4=M4#=""
B5,B24B5=""
D5D5=NA()
G10:G13G10=ACOUNT(A4:E6,2)
M10:N13M10=ACOUNT(A4:E6,2,1)
G18:K20G18=ACOUNT(A4:E6,1,1)
M18M18=ACOUNT(A4:E6,3)
J24:Q24J24=ACOUNT(A24:H24)
S24:S25S24=ACOUNT(A24:H24,2,0)
U27:V28U27=ACOUNT(A24:H24,2,3)
J28:Q28J28=ACOUNT(A24:H24,1)
D33:D40D33=ACOUNT(A33:A40)
I33:I40I33=ACOUNT(A33:A40,1)
M35:M38M35=ACOUNT(A33:A40,2)
P38:Q41P38=ACOUNT(A33:A40,2,1)
Dynamic array formulas.
 
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)
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRS
1The idea of the video was to get
2from this:tothis:
3LocationDepartmentLocationColumn1Column2Column3Column4Column5
4BrisbaneFinanceBrisbaneFinanceI.T.Logistics
5BrisbaneI.T.SydneyFinance
6BrisbaneLogisticsDublinFinanceI.T.LogisticsHR
7SydneyFinanceCopenhagenI.T.LogisticsHRMarketingDesign
8DublinFinanceLondonHRMarketingFinancemain trick of whole formula
9DublinI.T.ACOUNT does occurrence count of (q)C*(q-1)+m
10DublinLogistics1. calculating inner array rows nr. (R)2. rows sequence (q)3. clms sequeq.(m)+clms nr.(C)4. array sequence
11DublinHR=UNIQUE(A4:A19)=ROWS(D12#)=XMATCH(A4:A19,D12#)=ACOUNT(H12#,1)=MAX(K12#)=5*(H12#-1)+K12#
12CopenhagenI.T.Brisbane51(q)1(m)51
13CopenhagenLogisticsSydney(R)12(C)2
14CopenhagenHRDublin133
15CopenhagenMarketingCopenhagen216
16CopenhagenDesignLondon3111
17LondonHR3212
18LondonMarketing3313
19LondonFinance3414
204116
214217
224318
234419
24other functions4520
25AREPORT5121
265222
275323
285.inner array-xlookup6. cosmetics-final report
29=XLOOKUP(SEQUENCE(5,5),O12#,B4:B19,"")=AREPORT(E30#,D12#,"Column"&SEQUENCE(,5),,,"Deparment")
30FinanceI.T.LogisticsDeparmentColumn1Column2Column3Column4Column5
31FinanceBrisbaneFinanceI.T.Logistics
32FinanceI.T.LogisticsHRSydneyFinance
33I.T.LogisticsHRMarketingDesignDublinFinanceI.T.LogisticsHR
34HRMarketingFinanceCopenhagenI.T.LogisticsHRMarketingDesign
35LondonHRMarketingFinance
36single cell
37LocationColumn1Column2Column3Column4Column5
38BrisbaneFinanceI.T.Logistics
39SydneyFinance
40DublinFinanceI.T.LogisticsHR
41CopenhagenI.T.LogisticsHRMarketingDesign
42LondonHRMarketingFinance
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
RangeFormula
D11,F11,H11,K11,M11,O11,K29,E29D11=FORMULATEXT(D12)
D12:D16D12=UNIQUE(A4:A19)
F12F12=ROWS(D12#)
H12:H27H12=XMATCH(A4:A19,D12#)
K12:K27K12=ACOUNT(H12#,1)
M12M12=MAX(K12#)
O12:O27O12=5*(H12#-1)+K12#
E30:I34E30=XLOOKUP(SEQUENCE(5,5),O12#,B4:B19,"")
K30:P35K30=AREPORT(E30#,D12#,"Column"&SEQUENCE(,5),,,"Deparment")
D37:I42D37=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)
A44A44=FORMULATEXT(D37)
Dynamic array formulas.
 
Different output that should be considered when unpivoting. 2 ways with XMATCH and MAP. These work without ACOUNT.
Excel Formula: 
=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)
    )
)
Excel Formula: 
=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
ABCDEFGHIJK
1Different output, 2 ways
21. xmatch sol.
3LocationDepartmentLocationFinanceI.T.LogisticsHRMarketingDesign
4BrisbaneFinanceBrisbanexxx
5BrisbaneI.T.Sydneyx
6BrisbaneLogisticsDublinxxxx
7SydneyFinanceCopenhagenxxxxx
8DublinFinanceLondonxxx
9DublinI.T.
10DublinLogistics2. map sol.
11DublinHRLocationFinanceI.T.LogisticsHRMarketingDesign
12CopenhagenI.T.Brisbanexxx
13CopenhagenLogisticsSydneyx
14CopenhagenHRDublinxxxx
15CopenhagenMarketingCopenhagenxxxxx
16CopenhagenDesignLondonxxx
17LondonHR
18LondonMarketingother functions
19LondonFinanceAREPORT
20
ACOUNT post 6
Cell Formulas
RangeFormula
D3:J8D3=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:J16D11=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.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top