ASCAN

ASCAN(a,[d])
a
array
[d]
direction argument ; 0 or omitted scan by array, -1 by rows, 1 by clms
Array SCAN, 3 in 1 function, SCAN by row, by column, by array
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRST
1d,omitted (by array)d,-1 (by rows)d,1 (by clms)
2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)
3123413610136101234
45678152128365111826681012
5910111245556678919304215182124
613141516911051201361327425828323640
7
8d,omitted (by array)d,-1 (by rows)d,1 (by clms)
9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)
10123413610136101234
115678152128365111826681012
12910111245556678919304215182124
1313141516911051201361327425828323640
14171819201531711902101735547445505560
15212223242312532763002143669066727884
16
17Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post
Cell Formulas
RangeFormula
F2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)
F3:I6F3=ASCAN(A3:D6)
K3:N6K3=ASCAN(A3:D6,-1)
P3:S6P3=ASCAN(A3:D6,1)
A10:D15A10=SEQUENCE(6,4)
F10:I15F10=ASCAN(A10#)
K10:N15K10=ASCAN(A10#,-1)
P10:S15P10=ASCAN(A10#,1)
Dynamic array formulas.
 
Upvote 0
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Task: scan largest n values of each row of the sample array
2extract largest 4 values=INDEX(AFUSBYROW(A5:G14,,,-1),SEQUENCE(10),SEQUENCE(,C3))
3largest n=4row by row↓alternative functionresult to get:
4sample 10 rows=LARGE(A5:G5,SEQUENCE(,$C$3))↓single cell=SCAN(0,I5:L14,LAMBDA(v,a,v+a))
581991068919109919109919293847
6771618417418171671817167658298105
7378251018181087181087123133141148
81014711152012015141120151411168183197208
9291128111212121191212119220232243252
10521121113202013111120131111272285296307
11181141671116141181614118323337348356
12109102125712101091210109368378388397
13619119519141919141119191411416435449460
1411201114220152020151420201514480500515529
15
161st way, 2 lambda helper, SCAN(MAKEARRAY)
17=SCAN(0,MAKEARRAY(10,C3,LAMBDA(r,c,LET(x,INDEX(A5:G14,r,),LARGE(x,c)))),LAMBDA(v,a,v+a))
1819293847
19other function658298105
20AFUSBYROW123133141148
21168183197208
22220232243252
23272285296307
24323337348356
25368378388397
26416435449460
27480500515529
28
292nd way, 1 lambda helper, SCAN
30=SCAN(0,SEQUENCE(10,C3),LAMBDA(v,i,LET(x,INDEX(A5:G14,QUOTIENT(i-1,C3)+1,),v+LARGE(x,MOD(i-1,C3)+1))))
3119293847
32658298105
33123133141148
34168183197208
35220232243252
36272285296307
37323337348356
38368378388397
39416435449460
40480500515529
41
ASCAN post 11
Cell Formulas
RangeFormula
P2P2=FORMULATEXT(P5)
I4,I30,I17,V4I4=FORMULATEXT(I5)
P5:S14P5=INDEX(AFUSBYROW(A5:G14,,,-1),SEQUENCE(10),SEQUENCE(,C3))
V5:Y14V5=SCAN(0,I5:L14,LAMBDA(v,a,v+a))
I5:L14I5=LARGE(A5:G5,SEQUENCE(,$C$3))
I18:L27I18=SCAN(0,MAKEARRAY(10,C3,LAMBDA(r,c,LET(x,INDEX(A5:G14,r,),LARGE(x,c)))),LAMBDA(v,a,v+a))
I31:L40I31=SCAN(0,SEQUENCE(10,C3),LAMBDA(v,i,LET(x,INDEX(A5:G14,QUOTIENT(i-1,C3)+1,),v+LARGE(x,MOD(i-1,C3)+1))))
Dynamic array formulas.
 
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1largest n=4Task: scan whole array by each product's largest n values
2sample 10 rows3productsresults to get step by step
3
4unique prod.
5=TRANSPOSE(UNIQUE(A8:A17))filter prod A
6ABC=--(A8:A17=INDEX(A6#,1))1A
7prod.↓=K8*LARGE(B8:H8,SEQUENCE(,$D$1))=K8#*ASCAN(M8:P17)
8A819910689119109919293847
9B7716184174000000000
10A378251018118108765758390
11C101471115201000000000
12C2911281112000000000
13A52112111320120131111110123134145
14B1811416711000000000
15A109102125711210109157167177186
16C61911951914000000000
17B1120111422015000000000
18
19filter prod B
20result we should obtain=--(A8:A17=INDEX(A6#,2))2B
211A+2B+3C↓=K22*LARGE(B8:H8,SEQUENCE(,$D$1))=K22#*ASCAN(M22:P31)
22=R8#+R22#+R36#000000000
23192938471181716718355158
2418355158000000000
2565758390000000000
2620354960000000000
27728495104000000000
2811012313414511614118748899107
29748899107000000000
30157167177186000000000
31123142156167120201514127147162176
32127147162176
33filter prod C
34=--(A8:A17=INDEX(A6#,3))3C
35↓=K36*LARGE(B8:H8,SEQUENCE(,$D$1))=K36#*ASCAN(M36:P45)
36000000000
37000000000
38000000000
3912015141120354960
4011212119728495104
41000000000
42000000000
43000000000
44119191411123142156167
45000000000
46
ASCAN post 12
Cell Formulas
RangeFormula
A5,M35,R35,B22,M21,R21,M7,R7A5=FORMULATEXT(A6)
A6:C6A6=TRANSPOSE(UNIQUE(A8:A17))
K6,K34,K20K6=FORMULATEXT(K8)
K8:K17K8=--(A8:A17=INDEX(A6#,1))
R8:U17,R36:U45,R22:U31R8=K8#*ASCAN(M8:P17)
M8:P17M8=K8*LARGE(B8:H8,SEQUENCE(,$D$1))
K22:K31K22=--(A8:A17=INDEX(A6#,2))
B23:E32B23=R8#+R22#+R36#
M22:P31M22=K22*LARGE(B8:H8,SEQUENCE(,$D$1))
K36:K45K36=--(A8:A17=INDEX(A6#,3))
M36:P45M36=K36*LARGE(B8:H8,SEQUENCE(,$D$1))
Dynamic array formulas.
 
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Task: scan whole array by each product's largest n values
2single cell formulas 2 ways
3
4largest n=4
5sample 10 rows3productsunique prod.
6prod.=UNIQUE(A7:A16)
7A819910689A
8B7716184174B
9A378251018C
10C101471115201
11C2911281112
12A52112111320
13B1811416711
14A1091021257
15C61911951914
16B1120111422015
17
181st way, 3 lambda helper, REDUCE(SCAN(MAKEARRAY))
19 =REDUCE(0,SEQUENCE(D3),LAMBDA(v,j,v+SCAN(0, MAKEARRAY(10,C2,LAMBDA(r,c,LET(x,INDEX(B5:H14,r,),LARGE(x,c))))*(--(A5:A14=INDEX(J5#,j))), LAMBDA(v,a,v+a))*(--(A5:A14=INDEX(J5#,j)))))
2019293847
2118355158
2265758390
2320354960
24728495104
25110123134145
26748899107
27157167177186
28123142156167
29127147162176
30
312nd way, 2 lambda helper, REDUCE(SCAN)
32 =REDUCE(0,SEQUENCE(D3),LAMBDA(v,j,v+SCAN(0,SEQUENCE(10,C2), LAMBDA(v,i,LET(q,QUOTIENT(i-1,C2)+1,x,INDEX(B5:H14,q,)*INDEX(--(A5:A14=INDEX(J5#,j)),q), v+LARGE(x,MOD(i-1,C2)+1))))*(--(A5:A14=INDEX(J5#,j)))))
3319293847
3418355158
3565758390
3620354960
37728495104
38110123134145
39748899107
40157167177186
41123142156167
42127147162176
43
ASCAN post 13
Cell Formulas
RangeFormula
J6J6=FORMULATEXT(J7)
J7:J9J7=UNIQUE(A7:A16)
B20:E29B20=REDUCE(0,SEQUENCE(D5),LAMBDA(v,j,v+SCAN(0,MAKEARRAY(10,D4,LAMBDA(r,c,LET(x,INDEX(B7:H16,r,),LARGE(x,c))))*(--(A7:A16=INDEX(J7#,j))),LAMBDA(v,a,v+a))*(--(A7:A16=INDEX(J7#,j)))))
B33:E42B33=REDUCE(0,SEQUENCE(D5),LAMBDA(v,j,v+SCAN(0,SEQUENCE(10,D4),LAMBDA(v,i,LET(q,QUOTIENT(i-1,D4)+1,x,INDEX(B7:H16,q,)*INDEX(--(A7:A16=INDEX(J7#,j)),q),v+LARGE(x,MOD(i-1,D4)+1))))*(--(A7:A16=INDEX(J7#,j)))))
Dynamic array formulas.
 
This is how a lambda function will look (practice purpose), let's name it LARGESCAN(a,p,l) where a, array, p, product clm, l, l largest values
Excel Formula: 
=LAMBDA(a,p,l,LET(u,UNIQUE(p),w,ROWS(u),
   REDUCE(0,SEQUENCE(w),LAMBDA(v,j,LET(x,(--(p=INDEX(u,j))),v+
     SCAN(0,MAKEARRAY(10,l,LAMBDA(r,c,LET(x,INDEX(a,r,),LARGE(x,c))))*x,
      LAMBDA(v,a,v+a))*x)))))
LAMBDA 1.1.4.xlsx
ABCDEFGHIJK
1Task: scan whole array by each product's largest n values
2single cell (lambda function)
3
4sample
5prod.
6A819910689
7B7716184174
8A378251018
9C101471115201
10C2911281112
11A52112111320
12B1811416711
13A1091021257
14C61911951914
15B1120111422015
16
17a,B6:H15,p,A6:A15,l,4
18=LARGESCAN(B6:H15,A6:A15,4)
1919293847
2018355158
2165758390
2220354960
23728495104
24110123134145
25748899107
26157167177186
27123142156167
28127147162176
29
ASCAN post 13.1
Cell Formulas
RangeFormula
B18B18=FORMULATEXT(B19)
B19:E28B19=LARGESCAN(B6:H15,A6:A15,4)
Dynamic array formulas.
 
Examples with REDUCE as main lambda helper function.
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Task: Filter only the rows of an array that hold unique values, alternative functions
2dups1.1. AFUSBYROW (f , ignored,u,1,s,omitted)1.2. AHCLEAN(AFUSBYROW)
3sample=AFUSBYROW(A4:J23,,1)=AHCLEAN(L4#)
4252251920203492825219203492841110328714131620
5612231329197429861223132919748191142826829122010
618271825282625262218272528262219263027822122312
7191815192519411923191815254123187382520941426
84111032871413162041110328714131620
915161832115171881215161832117812
10232313227171925223132271719252. recursive method seen in ARRANGEMENTS thread
1112462771917272641246277191726=T_P(A4:J23)
1274178823616282874178236162841110328714131620
13212126821631139212682163119191142826829122010
1455232012381315232012813119263027822122312
15191142826829122010191142826829122010187382520941426
162920171231612122818292017123162818
1751429528162724305514292816272430
188307253091920195830725919205other functions
191926302782212231219263027822122312AFUSBYROW
202913539231027921291353923102721AHCLEAN
21187382520941426187382520941426T_P
222020292743141021222029274314102122
2316821252115151917151682125151917
24
ASCAN post 14
Cell Formulas
RangeFormula
L3,W11,W3L3=FORMULATEXT(L4)
L4:U23L4=AFUSBYROW(A4:J23,,1)
W4:AF7W4=AHCLEAN(L4#)
W12:AF15W12=T_P(A4:J23)
Dynamic array formulas.
 
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1Task: Filter only the rows of an array that holds unique values, REDUCE
2
3step 1 clm1=a + clm2=a +….+ clm10=a
4duplicatesrows unique values =REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27)))
5columns10⬇
6⬇step 2 sum BYROW(a')
7samplea⬇a'=BYROW(L8#,LAMBDA(a,SUM(a)))
82522519202034928212122111114a''
96122313291974298111121112112step 3 FILTER(a,a''=clms(a))
10182718252826252622212211222116=FILTER(A8:J27,W8#=D5)
1119181519251941192341141411412241110328714131620
1241110328714131620111111111110191142826829122010
1315161832115171881221211212111419263027822122312
142323132271719252221111121214187382520941426
151246277191727264121211121214
16741788236162828111221112214
17212126821631139221111212114
185523201238131221211211114
19191142826829122010111111111110
202920171231612122818111311331116
2151429528162724305311311111316
228307253091920195121121212114
2319263027822122312111111111110single cell
24291353923102792111112111211241110328714131620
25187382520941426111111111110191142826829122010
2620202927431410212222111111111219263027822122312
271682125211515191715112123311318187382520941426
28
29Y24:=FILTER(A8:J27,BYROW(REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27))),LAMBDA(x,SUM(x)=D5)))
30
ASCAN post 15
Cell Formulas
RangeFormula
L4L4=FORMULATEXT(L8)
W7,Y10W7=FORMULATEXT(W8)
L8:U27L8=REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27)))
W8:W27W8=BYROW(L8#,LAMBDA(a,SUM(a)))
Y11:AH14Y11=FILTER(A8:J27,W8#=D5)
Y24:AH27Y24=FILTER(A8:J27,BYROW(REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27))),LAMBDA(x,SUM(x)=D5)))
I29I29=FORMULATEXT(Y24)
Dynamic array formulas.
 
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Task: filter only rows of an array that are in ascending order, alternative functions
2
3asc.order1.1. all rows in asc. order1.2. compare a=a'1.3. whole rows comparing
4samplea=AFUSBYROW(A5:E12,,,1)=A5:E12=H5#=BYROW(N5#,LAMBDA(a,AND(a)))
5abcdea'abcdeTRUETRUETRUETRUETRUETRUE
6aebdeabdeeTRUEFALSEFALSEFALSETRUEFALSE1.4. step 4,filtering
71234512345TRUETRUETRUETRUETRUETRUE=FILTER(A5:E12,T5#)
81304501345FALSEFALSEFALSETRUETRUEFALSEabcde
9aabcdaabcdTRUETRUETRUETRUETRUETRUE12345
101223412234TRUETRUETRUETRUETRUETRUEaabcd
11ab123123abFALSEFALSEFALSEFALSEFALSEFALSE12234
12123ab123abTRUETRUETRUETRUETRUETRUE123ab
13
14single cell
15=FILTER(A5:E12,BYROW(A5:E12=AFUSBYROW(A5:E12,,,1),LAMBDA(a,AND(a))))
16other functionabcde
17AFUSBYROW12345
18T_CAaabcd
1912234
20123ab
21
222. recursive method seen in ARRANGEMENTS thread
23=T_CA(A5:E12)
24abcde
2512345
26aabcd
2712234
28123ab
29
ASCAN post 16
Cell Formulas
RangeFormula
H4,N4,H23,H15,V7,T4H4=FORMULATEXT(H5)
H5:L12H5=AFUSBYROW(A5:E12,,,1)
N5:R12N5=A5:E12=H5#
T5:T12T5=BYROW(N5#,LAMBDA(a,AND(a)))
V8:Z12V8=FILTER(A5:E12,T5#)
H16:L20H16=FILTER(A5:E12,BYROW(A5:E12=AFUSBYROW(A5:E12,,,1),LAMBDA(a,AND(a))))
H24:L28H24=T_CA(A5:E12)
Dynamic array formulas.
 
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQR
1Task: filter only rows of an array that are in ascending order, REDUCE
25columnsstep 1 <=> for i=1 to clms(a)-1, 1*(clm i<=clm i+1)
3asc.order=REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1))))
4sampleaa'
5abcde1
6aebde0step 2, FILTER(a,a')
7123451=FILTER(A5:E12,G5#)
8130450abcde
9aabcd112345
10122341aabcd
11ab123012234
12123ab1123ab
13
14
15step 3, single cell
16=FILTER(A5:E12,REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1)))))
17abcde
1812345
19aabcd
2012234
21123ab
22
ASCAN post 17
Cell Formulas
RangeFormula
G3G3=FORMULATEXT(G5)
G5:G12G5=REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1))))
I7,C16I7=FORMULATEXT(I8)
I8:M12I8=FILTER(A5:E12,G5#)
C17:G21C17=FILTER(A5:E12,REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1)))))
Dynamic array formulas.
 
Another cool example using SCAN versatility that deserves to be added to our collection, this time, inspired by latest video of Leila Gharani (4-Nov-21):
How to Use Excel's New SCAN Function (and attempting to calculate YTD values with it)
The idea is to SCAN the sales by calendar year (find ways to reset the "acumulator" by a certain condition)
Note: I have changed the sales values to be easier to check
leila SCAN.xlsx
ABCDEFGHIJKLMNOPQRST
1Leila's solution: ( notice the cool trick of 0*a+b, that resets the "acumulator" "a"))
2=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>1,a+b,0*a+b)))
3table sample : Sales↓↓↓my solution (longer, unfortunatily, but avoids offset, hope that still can be considered a lambda "formula" ?)
4MonthSales↓↓↓=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>1)*v+INDEX(Sales[Sales],i)))
5Jan-19111
6Feb-19233to reset the "acumulator" on July , we change only <>1 to <>7 on both formulas
7Mar-19366=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>7,a+b,0*a+b)))
8Apr-1941010↓↓↓
9May-1951515↓↓↓=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>7)*v+INDEX(Sales[Sales],i)))
10Jun-196212111
11Jul-197282833
12Aug-198363666
13Sep-19945451010
14Oct-191055551515
15Nov-191166662121
16Dec-1912787877
17Jan-201111515
18Feb-202332424
19Mar-203663434
20Apr-20410104545
21May-20515155757
22Jun-20621215858
23Jul-20728286060
24Aug-20836366363
25Sep-20945456767
26Oct-201055557272
27Nov-201166667878
28Dec-2012787877
29Jan-211111515
30Feb-212332424
31Mar-213663434
32Apr-21410104545
33May-21515155757
34Jun-21621215858
35Jul-21728286060
36Aug-21836366363
37Sep-21945456767
38Oct-211055557272
39Nov-211166667878
40Dec-2112787877
41Jan-221111515
42Feb-222332424
43Mar-223663434
44Apr-22410104545
45May-22515155757
46Jun-22621215858
47Jul-22728286060
48Aug-22836366363
49Sep-22945456767
50Oct-221055557272
51Nov-221166667878
52Dec-2212787877
531515
542424
553434
564545
575757
58
Sheet2
Cell Formulas
RangeFormula
D2,H7D2=FORMULATEXT(D5)
F4,J9F4=FORMULATEXT(F5)
D5:D52D5=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>1,a+b,0*a+b)))
F5:F52F5=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>1)*v+INDEX(Sales[Sales],i)))
H10:H57H10=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>7,a+b,0*a+b)))
J10:J57J10=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>7)*v+INDEX(Sales[Sales],i)))
Dynamic array formulas.
 
leila SCAN.xlsx
ABCDEFGHIJKLMNOPQR
1Bonus trick: If we want to SCAN by months and not years, a REDUCE(SCAN)) lambda "formula" (every month acumulates values of previous records of same month)
2There are more approaches, chose this one because is with SCAN embeded in REDUCE
3MonthSales=REDUCE(0,SEQUENCE(12),LAMBDA(v,i,LET(x,IF(MONTH(Sales[Month])=i,1),v+IF(x,SCAN(0,IF(x,Sales[Sales]),LAMBDA(a,b,a+b))))))
4Jan-1911
5Feb-1922
6Mar-1933
7Apr-1944
8May-1955
9Jun-1966
10Jul-1977
11Aug-1988
12Sep-1999
13Oct-191010
14Nov-191111
15Dec-191212
16Jan-2012
17Feb-2024
18Mar-2036
19Apr-2048
20May-20510
21Jun-20612
22Jul-20714
23Aug-20816
24Sep-20918
25Oct-201020
26Nov-201122
27Dec-201224
28Jan-2113
29Feb-2126
30Mar-2139
31Apr-21412
32May-21515
33Jun-21618
34Jul-21721
35Aug-21824
36Sep-21927
37Oct-211030
38Nov-211133
39Dec-211236
40Jan-2214
41Feb-2228
42Mar-22312
43Apr-22416
44May-22520
45Jun-22624
46Jul-22728
47Aug-22832
48Sep-22936
49Oct-221040
50Nov-221144
51Dec-221248
52
Sheet3
Cell Formulas
RangeFormula
D3D3=FORMULATEXT(D4)
D4:D51D4=REDUCE(0,SEQUENCE(12),LAMBDA(v,i,LET(x,IF(MONTH(Sales[Month])=i,1),v+IF(x,SCAN(0,IF(x,Sales[Sales]),LAMBDA(a,b,a+b))))))
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