MicrosoftHere are some interesting ways of using Dynamic Array Formulas
Sample File
https://1drv.ms/f/s!AiKBTsYfZw-vgrtkdDXnLzxgimxokg
1. Multi Criteria SUMIFS / COUNTIFS
=SUMIFS(D[COST],D[CLIENT],INDEX(N2#,,1),D[REGION],INDEX(N2#,,2))
2. Multi Column Sort
=SORT(D,{1,2,3,4},{1,1,1,-1})
3. Sort by Custom List e.g North, South, East, West or Jan, Feb, Mar..
=SORTBY(D,D[CLIENT],1,MATCH(D[REGION],CustList,0),1,D[COST],-1)
CustList is either a named Range containing North, South, East, West or an array Constant
4. Emulate Wild cards in the Filter Function
a) Contains (*Text*)
=FILTER(D,IFERROR(SEARCH(K1,D[CLIENT]),0),"No Data")
b) Begins with (Text*)
=FILTER(D,IFERROR(LEFT(D[CLIENT],LEN(Q1))=Q1,0),"No Data")
c) Ends with (*Text)
=FILTER(D,IFERROR(RIGHT(D[CLIENT],LEN(V1))=V1,0),"No Data")
5. Clients with Characters (=???)
=FILTER(D,IFERROR(LEN(D[CLIENT])=LEN(AC1),0),"No Data")
6. Extract a user defined number of Random Records from a Dataset
=INDEX(D,RandIndex,SEQUENCE(,COUNTA(D[#Headers])))
Where RandIndex = NDEX(SORTBY(SEQUENCE(ROWS(D)),RANDARRAY(COUNTA(SEQUENCE(ROWS(D))))),SEQUENCE(NoOfSamples))
7. Multiple OR Criteria
a) Clients : ABB or BHEL or ORACLE
=FILTER(F,COUNTIFS(mClients,F[CLIENT]))
b) BU : US or DE or GB
=FILTER(F,MMULT(IFERROR(--(SEARCH(TRANSPOSE(mBU),F[BU])=1),0),SEQUENCE(COUNTA(mBU),,,0)))
c) Cost (> 10 and < 20) or (>90 and <100)
=SORT(FILTER(F,((F[COST]>10)*(F[COST]<20))+((F[COST]>90)*(F[COST]<100))),5)
8. Remove and Rearrange Columns
=INDEX(F,SEQUENCE(ROWS(F)),{3,5,1})
Enjoy
Sam
