# APIVOT  	array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table, no refresh needed



## Xlambda (Apr 11, 2021)

APIVOT array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table. calls AAGGREGATE , APPEND2V , APPEND2H
Basic functionality means: as in PT interface we can select rows values, column values, values, value fields, only sum, count, min and max
- disadvantages: was not designed to compete with mighty PT, does not have grouping functionality as PT does, though, could be added but the formulas can get discouraging huge.
- advantages: no table format needed, no refresh needed, so if we filter a large table for some complex criteria in a simple dynamic array, this lambda can be handy.
Other functions on minisheet ASELECT
*Note:* Sample data, courtesy of Mike Girvin ExcelIsFun

```
=LAMBDA(a,rw,cl,vl,vf,
    LET(g,"Grand Total",fn,IF(vf=0,0,vf),q,SEQUENCE(,COLUMNS(a)),c,INDEX(a,,cl),r,INDEX(a,,rw),v,INDEX(a,,vl),uc,SORT(UNIQUE(c)),ur,SORT(UNIQUE(r)),tc,TRANSPOSE(uc),f,IF(AND(ISNUMBER(v)),fn,1),
       ar,SWITCH(f,0,SUMIFS(v,r,ur,c,tc),2,MINIFS(v,r,ur,c,tc),3,MAXIFS(v,r,ur,c,tc),1,COUNTIFS(r,ur,c,tc)),fg,SWITCH(f,2,0,3,1,4,7,5),
       rc,AAGGREGATE(ar,fg,),br,TRANSPOSE(AAGGREGATE(TRANSPOSE(ar),fg,)),
       w,ROWS(ar)+1,u,COLUMNS(ar)+2,sr,SEQUENCE(w),sc,SEQUENCE(,u)-1,lc,IF(sr=w,g,INDEX(ur,sr)),tr,SWITCH(sc,0,"("&rw&"\"&cl&")"&" "&vl&" vf="&fn,u-1,g,INDEX(tc,sc)),
       aa,APPEND2V(APPEND2H(ar,rc,),br,),ab,IF(aa="",SWITCH(f,2,MIN(br,rc),3,MAX(br,rc),SUM(rc)),aa),ac,APPEND2V(tr,APPEND2H(lc,ab,),),
       IFS(ISNA(XMATCH(fn,{0,1,2,3})),"check function nr.",NOT(AND(ISNUMBER(XMATCH(CHOOSE({1,2,3},rw,cl,vl),q)))),"check selection",TRUE,ac)
    )
)
```
LAMBDA 7.0.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1sample data, courtesy of Mike GirvinExcelIsFun=APIVOT(Rev,2,4,6,)vf=0sumSum of Net RevenueColumn Labels2DateSales RepUnitsProductRegionNet Revenue(2\4) 6 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total301-01-20Tynia Malone500QuadMidWest12560.88Chantel Mims7659.578379.3328169.7129746.5913486.9687442.16Chantel Mims7659.578379.3328169.7129746.61348787442.16401-01-20Kiki Sho38YanakiMidWest902.79Hien Pham7785.007809.9422079.839952.8819538.6867166.33Hien Pham77857809.9422079.839952.8819538.767166.33501-01-20Hien Pham500YanakiMidWest8035.63Janis Figueroa161.701104.4218189.425683.153039.7528178.44Janis Figueroa161.71104.4218189.425683.153039.7528178.44601-01-20Chantel Mims500YanakiWest8035.63Kiki Sho9137.046161.932809.605981.8010598.5734688.94Kiki Sho9137.046161.932809.65981.810598.634688.94701-01-20Tynia Malone39QuadEast1481.47Tynia Malone948.47339.5038904.912434.6314517.6357145.14Tynia Malone948.47339.538904.912434.6314517.657145.14801-01-20Kiki Sho48SunsetMidWest934.2Grand Total25691.7823795.12110153.553799.0561181.59274621.01Grand Total25691.7823795.12110153.4753799.161181.6274621.01901-01-20Hien Pham13QuadCanada580.971002-01-20Tynia Malone27YanakiNorthWest641.45=APIVOT(Rev,2,4,6,1)vf=1count1102-01-20Hien Pham13SunsetMidWest337.35(2\4) 6 vf=1Crested BeautMajestic BeautQuadSunsetYanakiGrand Total1202-01-20Chantel Mims36SunsetMidWest732.87Chantel Mims75676311302-01-20Tynia Malone44QuadSouth1596.61Hien Pham24567241403-01-20Hien Pham100Crested BeautMidWest1816.5Janis Figueroa12421101503-01-20Chantel Mims7YanakiMexico188.65Kiki Sho52345191603-01-20Hien Pham5YanakiMexico139.75Tynia Malone11773191703-01-20Chantel Mims37QuadMexico1374.05Grand Total16142526221031803-01-20Janis Figueroa525QuadMexico13188.921903-01-20Chantel Mims450SunsetNorthWest6714.56=APIVOT(Rev,2,4,6,2)vf=2minMin of Net RevenueColumn Labels2004-01-20Hien Pham45SunsetCanada916.09(2\4) 6 vf=2Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total2104-01-20Tynia Malone3SunsetWest74.85Chantel Mims215.6209.7312.83639.6726.9526.95Chantel Mims215.6209.7312.83639.6726.9526.952204-01-20Chantel Mims48Crested BeautWest934.2Hien Pham1816.533.95218.4524.9555.924.95Hien Pham1816.533.95218.4524.9555.924.952304-01-20Chantel Mims100Crested BeautMidWest1746.5Janis Figueroa161.734.9585.38174.653039.7534.95Janis Figueroa161.734.9585.38174.653039.7534.952404-01-20Hien Pham2YanakiSouth55.9Kiki Sho249.5305.55305.83311.457.957.9Kiki Sho249.5305.55305.83311.457.957.92504-01-20Kiki Sho25QuadMexico907.16Tynia Malone948.47339.5298.8374.85641.4574.85Tynia Malone948.47339.5298.8374.85641.4574.852604-01-20Chantel Mims8Crested BeautSouth215.6Grand Total161.733.9585.3824.9526.9524.95Grand Total161.733.9585.3824.9526.9524.952704-01-20Kiki Sho34Crested BeautMidWest778.862804-01-20Chantel Mims275YanakiWest4419.59=APIVOT(Rev,2,4,6,3)vf=3max2905-01-20Chantel Mims200QuadEast5976.6(2\4) 6 vf=3Crested BeautMajestic BeautQuadSunsetYanakiGrand Total3005-01-20Hien Pham600YanakiEast9297.75Chantel Mims2724.756291.2514114.387531.788035.6314114.383105-01-20Janis Figueroa36Majestic BeautMidWest1069.47Hien Pham5968.56291.2513816.967574.199297.7513816.963205-01-20Chantel Mims28QuadMidWest1039.82Janis Figueroa161.71069.4713188.925508.53039.7513188.923305-01-20Kiki Sho525YanakiMidWest8437.41Kiki Sho5021.195856.381596.614191.258437.418437.413405-01-20Chantel Mims6Majestic BeautCanada209.7Tynia Malone948.47339.512560.88934.27633.8412560.883505-01-20Kiki Sho9Majestic BeautWest305.55Grand Total5968.56291.2514114.387574.199297.7514114.383605-01-20Hien Pham550QuadMidWest13816.963706-01-20Kiki Sho250SunsetEast4191.25=APIVOT(Rev,2,4,3,)Sum of Units2Column Labels3806-01-20Tynia Malone7QuadCanada298.83(2\4) 3 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total3906-01-20Chantel Mims150Crested BeautCanada2724.75Chantel Mims401321102220058174566Chantel Mims4013211022200581745664006-01-20Tynia Malone43Crested BeautMidWest948.47Hien Pham50029885666011893503Hien Pham500298856660118935034107-01-20Kiki Sho21SunsetNorthWest544.95Janis Figueroa6376774071501277Janis Figueroa63767740715012774207-01-20Hien Pham13QuadWest554.97Kiki Sho560309763316141890Kiki Sho5603097633161418904307-01-20Tynia Malone24SunsetWest529.38Tynia Malone431014711118772512Tynia Malone4310147111187725124407-01-20Tynia Malone15SunsetMexico389.25Grand Total151097541023514364713748Grand Total1510975410235143647137484508-01-20Kiki Sho2YanakiMidWest57.94608-01-20Tynia Malone4SunsetNorthWest99.8=APIVOT(Rev,4,5,3,)Sum of UnitsColumn Labels4708-01-20Kiki Sho42YanakiEast997.82(4\5) 3 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total4808-01-20Chantel Mims500SunsetEast7460.63Crested Beaut1501694112830408891510Crested Beaut1501694112834088915104908-01-20Hien Pham38Majestic BeautEast1161.19Majestic Beaut15381033657259260975Majestic Beaut153810336572592609755008-01-20Tynia Malone48SunsetMexico934.2Quad170241638107827512554454102Quad1702416381078275125544541025108-01-20Chantel Mims29SunsetSouth639.67Sunset55802664497482299853514Sunset558026644974822998535145209-01-20Hien Pham45YanakiCanada1069.09Yanaki458242110655393777763647Yanaki4582421106553937777636475309-01-20Tynia Malone10SunsetCanada239.5Grand Total43520741744325913532328255513748Grand Total435207417443259135323282555137485409-01-20Kiki Sho7QuadWest305.835509-01-20Janis Figueroa6Crested BeautMidWest161.7=APIVOT(Rev,4,5,6,)Sum of Net RevenueColumn Labels5609-01-20Chantel Mims1YanakiWest26.95(4\5) 6 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total5709-01-20Tynia Malone475YanakiNorthWest7633.84Crested Beaut2724.753153.696338.655452.0306184.11838.5625691.78Crested Beaut2724.753153.696338.655452.036184.11838.5625691.785809-01-20Chantel Mims33Crested BeautMexico699.85Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.125909-01-20Janis Figueroa75QuadCanada2457.56Quad5794.927543.4517379.5727417.666908.4833327.4711781.9110153.47Quad5794.927543.4517379.627417.666908.4833327.511781.9110153.476009-01-20Chantel Mims50Majestic BeautNorthWest1348.13Sunset1155.5912777.589997.947512.927533.96639.6714181.453799.05Sunset1155.5912777.69997.947512.927533.96639.6714181.453799.056110-01-20Janis Figueroa7SunsetNorthWest174.65Yanaki1069.0914095.56579.9517433.739222.856298.2412482.261181.59Yanaki1069.0914095.6579.9517433.739222.856298.2412482.261181.596210-01-20Chantel Mims575QuadSouth14114.38Grand Total11268.638731.4734635.6164742.1925264.0753064.2846914.8274621.01Grand Total11268.638731.534635.664742.1925264.0753064.346914.8274621.016310-01-20Janis Figueroa150YanakiEast3039.756411-01-20Kiki Sho300Majestic BeautMidWest5856.386511-01-20Janis Figueroa400SunsetMidWest5508.5dynamic APIVOT interface usingASELECT(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)6611-01-20Chantel Mims32YanakiEast760.24date 1date 2row startrow end6711-01-20Chantel Mims28Crested BeautMexico617.6103-01-2005-01-2012276811-01-20Kiki Sho41Crested BeautWest904.36=APIVOT(H70#,1,2,3,)6911-01-20Chantel Mims425SunsetWest5852.78=ASELECT(Rev,J67,K67,,,{2,4,6})(1\2) 3 vf=0Crested BeautQuadSunsetYanakiGrand Total7012-01-20Chantel Mims6Majestic BeautNorthWest215.7Hien PhamCrested Beaut1816.5Chantel Mims2896.37350.656714.564608.2421569.757112-01-20Hien Pham1Majestic BeautWest33.95Chantel MimsYanaki188.65Hien Pham1816.50916.09195.652928.247212-01-20Chantel Mims40SunsetEast814.3Hien PhamYanaki139.75Janis Figueroa013188.920013188.927312-01-20Hien Pham28YanakiNorthWest689.01Chantel MimsQuad1374.05Kiki Sho778.86907.16001686.027412-01-20Chantel Mims34Crested BeautEast721.06Janis FigueroaQuad13188.92Tynia Malone0074.85074.857512-01-20Hien Pham550SunsetMexico7574.19Chantel MimsSunset6714.56Grand Total5491.6621446.737705.54803.8939447.787613-01-20Chantel Mims7QuadMexico312.83Hien PhamSunset916.097713-01-20Hien Pham9YanakiMexico251.55Tynia MaloneSunset74.857813-01-20Kiki Sho10Crested BeautEast249.5Chantel MimsCrested Beaut934.2top left corner cell format7913-01-20Chantel Mims250Majestic BeautSouth6291.25Chantel MimsCrested Beaut1746.5(rows\columns) values value field=8013-01-20Hien Pham9Majestic BeautSouth323.55Hien PhamYanaki55.9kept it in this shape because if we pivot regular arrays, they do not have headers8113-01-20Tynia Malone375YanakiSouth6242.34Kiki ShoQuad907.168214-01-20Tynia Malone425QuadSouth10921.12Chantel MimsCrested Beaut215.68314-01-20Hien Pham275QuadNorthWest6908.48Kiki ShoCrested Beaut778.868414-01-20Kiki Sho44QuadMexico1596.61Chantel MimsYanaki4419.598514-01-20Janis Figueroa1Majestic BeautNorthWest34.95Chantel MimsQuad5976.68614-01-20Kiki Sho125Crested BeautEast2183.13APIVOT postCell FormulasRangeFormulaH1,H69,L68,H55,H46,H37,H28,H19,H10H1=FORMULATEXT(H2)H2:N8H2=APIVOT(Rev,2,4,6,)H11:N17H11=APIVOT(Rev,2,4,6,1)H20:N26H20=APIVOT(Rev,2,4,6,2)H29:N35H29=APIVOT(Rev,2,4,6,3)H38:N44H38=APIVOT(Rev,2,4,3,)H47:P53H47=APIVOT(Rev,4,5,3,)H56:P62H56=APIVOT(Rev,4,5,6,)J67J67=XMATCH(H67,Rev[Date],1)K67K67=XMATCH(I67,Rev[Date],-1)L69:Q75L69=APIVOT(H70#,1,2,3,)H70:J85H70=ASELECT(Rev,J67,K67,,,{2,4,6})Dynamic array formulas.


----------



## GeertD (Apr 11, 2021)

Yes! That's one of the goals! You beat me to it. 
I'll try to complete my Lambda function library in this regard, where I will try to avoid recursion, rather than search it out.
That way people can see two schools of thought.  
You've got a great set of functions, BTW! It takes me considerable time to figure out how they work...


----------



## Xlambda (Apr 11, 2021)

Haha, you are too kind! I see no competition here, no prizes. ? We share and we learn from each other.
On the other hand, me, I will always go for recursive solutions . I am fascinated by the paradox that holds, it is complicated because is too simple.
Iterative power will come, it's the future. ?
 Somehow , the famous quote of Richard Feynman comes to my mind, has a similar flavor:
"If you think you understand quantum mechanics, you don't understand quantum mechanics.


----------



## Xlambda (Apr 11, 2021)

If you were concerned of recursive functions called in APIVOT formula , is no case, because they come into action after the pivot data gets calculated.
The table I have used the formula for, had initially around 2500 rows and everything works fine. ?✌
Unless you pivot array will have 200x200 dimension , which I really doubt it. Any recursive components are safe with already extracted data.


----------



## GeertD (Apr 11, 2021)

Don't worry, I'm not 'concerned' about recursive formulas at all: they are very useful and appear in many fields of interest (e.g. try chaos theory, but to name one).
Furthermore, I'm very confident your formulas work fine and are well equipped for the task they're designed for. (Yes, we all understand the limitations of stack-depth, and all that.)
We also know (you said it quite often) that you are really into recursive formulas: I respect and appreciate that. Not too many people choose that route since it is not the easiest to understand.
Me OTOH, in a similar fashion, I'm into (and quite fascinated by) vectorization, ever since I 'discovered' it in the late 1980's.
So, you can imagine my enthusiasm when I learned about the new –I mean: current– Excel calculation engine and Dynamic Arrays: it's got vectorization written all over it!!  
And that is why I choose that route: it is my fascination, just like recursion is yours.
Furthermore: for each of us to pursue our own fascination, together we cover more ground for all to discover and learn about.
PS: the 'competition' element I put in here above is not a real competition as such, but more of a stimulus for all of us to build up this Lambda Library, and fill it with many useful, beautiful and powerful functions for everyone to benefit from. Yes, I know: I'm naïve like that.


----------



## Xlambda (Apr 12, 2021)

Vectorization is cool !!!✌?
Only time will tell what will happen. My humble opinion: in a couple of years AI will take over and probably, our formulas will become obsolete. Till then, let's live the illusion that we do something useful.?


----------



## Xlambda (Mar 23, 2022)

Finally, today, got the 15-Mar-22 Excel insiders update, the new 14 functions. VSTACK, HSTACK, etc..
A very simple pivot table function, inspired by Mike's latest YT: 
WRAPCOL & SORTBY Array Functions to Pivot A Table. "Robustify" your formulas! Excel Magic Trick 1784
Incredible how functions can evolve along with proper powerful new function updates. No need to call any of my custom-made functions. All the old functions will need updating with the new functions.
*PIV(r,c,v,[cs],[t]) *calls only Excel new build in functions like, MAP, TOROW, VSTACK, HSTACK
r: rows array ; c: columns array : v: values array (1D vertical arrays same dimension)
[cs]: count/sum argument, if omitted or 0, sums, if <>0 or 1, counts
[t]: text header top left cell


```
=LAMBDA(r,c,v,[cs],[t],
    LET(u,SORT(UNIQUE(r)),q,TOROW(SORT(UNIQUE(c))),w,ROWS(u),l,COLUMNS(q),
        m,MAP(REPT(u,SEQUENCE(,l)^0),REPT(q,SEQUENCE(w)^0),LAMBDA(a,b,SUM((a=r)*(b=c)*IF(cs,1,v)))),
        VSTACK(HSTACK(IF(ISOMITTED(t),"",t),q),HSTACK(u,m))
    )
)
```
EMT1784-1785.xlsmABCDEFGHIJKLMNOPQ1Note: Used arrays instead of table, unsorted2cs,omitted => sumscs,omitted => sums3sample (no dups)=PIV(B5:B19,C5:C19,D5:D19,,"Student/Quiz")=PIV(C5:C19,B5:B19,D5:D19,,"Quiz/Student")4NamesQuizQuize ScoresStudent/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy5TimmyQuiz 24Chantel9104Quiz 19552106JuneQuiz 32Devonte536Quiz 210341047ChantelQuiz 210June542Quiz 3462988JuneQuiz 24Sioux21099SiouxQuiz 12Timmy104810DevonteQuiz 1511ChantelQuiz 34cs,1 => countscs,1 => counts12DevonteQuiz 36=PIV(B5:B19,C5:C19,D5:D19,1,"Student/Quiz")=PIV(C5:C19,B5:B19,D5:D19,1,"Quiz/Student")13SiouxQuiz 39Student/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy14DevonteQuiz 23Chantel111Quiz 11111115TimmyQuiz 110Devonte111Quiz 21111116SiouxQuiz 210June111Quiz 31111117ChantelQuiz 19Sioux11118TimmyQuiz 38Timmy11119JuneQuiz 1520All 1's => there are no dups2122cs,omitted => sumscs,omitted => sums23sample (dups)=PIV(B25:B40,C25:C40,D25:D40,,"Student/Quiz")=PIV(C25:C40,B25:B40,D25:D40,,"Quiz/Student")24NamesQuizQuize ScoresStudent/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy25TimmyQuiz 24Chantel9104Quiz 195100521026JuneQuiz 32Devonte536Quiz 2103410427ChantelQuiz 210June100542Quiz 34629828JuneQuiz 24Sioux210929SiouxQuiz 12Timmy104830DevonteQuiz 1531ChantelQuiz 34cs,1 => countscs,1 => counts32DevonteQuiz 36=PIV(B25:B40,C25:C40,D25:D40,1,"Student/Quiz")=PIV(C25:C40,B25:B40,D25:D40,1,"Quiz/Student")33SiouxQuiz 39Student/QuizQuiz 1Quiz 2Quiz 3Quiz/StudentChantelDevonteJuneSiouxTimmy34DevonteQuiz 23Chantel111Quiz 11121135TimmyQuiz 110Devonte111Quiz 21111136SiouxQuiz 210June211Quiz 31111137ChantelQuiz 19Sioux11138TimmyQuiz 38Timmy11139JuneQuiz 1540JuneQuiz 110004142added a record to have also dups43Sheet1Cell FormulasRangeFormulaF3,K3,F32,K32,F23,K23,F12,K12F3=FORMULATEXT(F4)F4:I9F4=PIV(B5:B19,C5:C19,D5:D19,,"Student/Quiz")K4:P7K4=PIV(C5:C19,B5:B19,D5:D19,,"Quiz/Student")F13:I18F13=PIV(B5:B19,C5:C19,D5:D19,1,"Student/Quiz")K13:P16K13=PIV(C5:C19,B5:B19,D5:D19,1,"Quiz/Student")F24:I29F24=PIV(B25:B40,C25:C40,D25:D40,,"Student/Quiz")K24:P27K24=PIV(C25:C40,B25:B40,D25:D40,,"Quiz/Student")F33:I38F33=PIV(B25:B40,C25:C40,D25:D40,1,"Student/Quiz")K33:P36K33=PIV(C25:C40,B25:B40,D25:D40,1,"Quiz/Student")Dynamic array formulas.


----------



## Xlambda (Mar 28, 2022)

*PIV alternative*, with MAKEARRAY instead of MAP

```
=LAMBDA(r,c,v,[cs],[t],
    LET(u,SORT(UNIQUE(r)),q,TOROW(SORT(UNIQUE(c))),w,ROWS(u),l,COLUMNS(q),
           m,MAKEARRAY(w,l,LAMBDA(a,b,SUM((INDEX(u,a)=r)*(INDEX(q,b)=c)*IF(cs,1,v)))),
          VSTACK(HSTACK(IF(ISOMITTED(t),"",t),q),HSTACK(u,m))
        )
)
```
APIVOT.xlsxABCDEFGHIJKLMNOPQRS1PIV Concepts MAP,MAKEARRAY2rcv3NamesQuizQuize Scores=SORT(UNIQUE(B4:B19))4TimmyQuiz 24↓↓↓↓=TOROW(SORT(UNIQUE(C4:C19)))5JuneQuiz 32rows\clmsQuiz 1Quiz 2Quiz 3q6ChantelQuiz 210Chantel7JuneQuiz 24Devonte8SiouxQuiz 12June9DevonteQuiz 15Sioux10ChantelQuiz 34uTimmy11DevonteQuiz 3612SiouxQuiz 39MAP13DevonteQuiz 23If we use 2 arrays arguments with MAP is ideal to "equalize" them.14TimmyQuiz 110=REPT(G6#,SEQUENCE(,3)^0)=REPT(H5#,SEQUENCE(5)^0)15SiouxQuiz 210ChantelChantelChantelQuiz 1Quiz 2Quiz 316ChantelQuiz 19DevonteDevonteDevonteQuiz 1Quiz 2Quiz 317TimmyQuiz 38JuneJuneJuneQuiz 1Quiz 2Quiz 318JuneQuiz 15SiouxSiouxSiouxQuiz 1Quiz 2Quiz 319JuneQuiz 11000TimmyTimmyTimmyQuiz 1Quiz 2Quiz 32021to countto sum22=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19))))=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19)*D4:D19)))23111910424111536252111005422611121092711110482829MAKEARRAY30to count31=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19))))3211133111to sum34211=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19)*D4:D19)))351119104361115363710054238210939104840PIV ConceptCell FormulasRangeFormulaG3G3=FORMULATEXT(G6)H4,K14,G14H4=FORMULATEXT(H5)H5:J5H5=TOROW(SORT(UNIQUE(C4:C19)))G6:G10G6=SORT(UNIQUE(B4:B19))G15:I19G15=REPT(G6#,SEQUENCE(,3)^0)K15:M19K15=REPT(H5#,SEQUENCE(5)^0)E22,K34,E31E22=FORMULATEXT(G23)L22L22=FORMULATEXT(M23)G23:I27G23=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19))))M23:O27M23=MAP(G15#,K15#,LAMBDA(a,b,SUM((a=B4:B19)*(b=C4:C19)*D4:D19)))G32:I36G32=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19))))M35:O39M35=MAKEARRAY(5,3,LAMBDA(a,b,SUM((INDEX(G6#,a)=B4:B19)*(INDEX(H5#,b)=C4:C19)*D4:D19)))Dynamic array formulas.


----------



## realflexyourdata (Apr 16, 2022)

Love this stuff. I wonder how difficult it would be to allow nested column and row headers.


----------



## Xlambda (Apr 19, 2022)

realflexyourdata said:


> Love this stuff. I wonder how difficult it would be to allow nested column and row headers.


Thanks a lot! These days, Excel has so many versatile tools that nothing is too difficult anymore. Pivot Tables functionality can be reproduced by formulas or custom functions almost entirely. 
The question is why to do that? A common answer will be: no refresh needed.  We can write functions not only to overcome this drawback, our PT alike lambdas can be designed to work with all spectrum of functions, even with functions that return more than a single result. Hopefully, at the end of the week I will post some. Thanks again. Your feedback is very much appreciated!!


----------



## Xlambda (Apr 11, 2021)

APIVOT array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table. calls AAGGREGATE , APPEND2V , APPEND2H
Basic functionality means: as in PT interface we can select rows values, column values, values, value fields, only sum, count, min and max
- disadvantages: was not designed to compete with mighty PT, does not have grouping functionality as PT does, though, could be added but the formulas can get discouraging huge.
- advantages: no table format needed, no refresh needed, so if we filter a large table for some complex criteria in a simple dynamic array, this lambda can be handy.
Other functions on minisheet ASELECT
*Note:* Sample data, courtesy of Mike Girvin ExcelIsFun

```
=LAMBDA(a,rw,cl,vl,vf,
    LET(g,"Grand Total",fn,IF(vf=0,0,vf),q,SEQUENCE(,COLUMNS(a)),c,INDEX(a,,cl),r,INDEX(a,,rw),v,INDEX(a,,vl),uc,SORT(UNIQUE(c)),ur,SORT(UNIQUE(r)),tc,TRANSPOSE(uc),f,IF(AND(ISNUMBER(v)),fn,1),
       ar,SWITCH(f,0,SUMIFS(v,r,ur,c,tc),2,MINIFS(v,r,ur,c,tc),3,MAXIFS(v,r,ur,c,tc),1,COUNTIFS(r,ur,c,tc)),fg,SWITCH(f,2,0,3,1,4,7,5),
       rc,AAGGREGATE(ar,fg,),br,TRANSPOSE(AAGGREGATE(TRANSPOSE(ar),fg,)),
       w,ROWS(ar)+1,u,COLUMNS(ar)+2,sr,SEQUENCE(w),sc,SEQUENCE(,u)-1,lc,IF(sr=w,g,INDEX(ur,sr)),tr,SWITCH(sc,0,"("&rw&"\"&cl&")"&" "&vl&" vf="&fn,u-1,g,INDEX(tc,sc)),
       aa,APPEND2V(APPEND2H(ar,rc,),br,),ab,IF(aa="",SWITCH(f,2,MIN(br,rc),3,MAX(br,rc),SUM(rc)),aa),ac,APPEND2V(tr,APPEND2H(lc,ab,),),
       IFS(ISNA(XMATCH(fn,{0,1,2,3})),"check function nr.",NOT(AND(ISNUMBER(XMATCH(CHOOSE({1,2,3},rw,cl,vl),q)))),"check selection",TRUE,ac)
    )
)
```
LAMBDA 7.0.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1sample data, courtesy of Mike GirvinExcelIsFun=APIVOT(Rev,2,4,6,)vf=0sumSum of Net RevenueColumn Labels2DateSales RepUnitsProductRegionNet Revenue(2\4) 6 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total301-01-20Tynia Malone500QuadMidWest12560.88Chantel Mims7659.578379.3328169.7129746.5913486.9687442.16Chantel Mims7659.578379.3328169.7129746.61348787442.16401-01-20Kiki Sho38YanakiMidWest902.79Hien Pham7785.007809.9422079.839952.8819538.6867166.33Hien Pham77857809.9422079.839952.8819538.767166.33501-01-20Hien Pham500YanakiMidWest8035.63Janis Figueroa161.701104.4218189.425683.153039.7528178.44Janis Figueroa161.71104.4218189.425683.153039.7528178.44601-01-20Chantel Mims500YanakiWest8035.63Kiki Sho9137.046161.932809.605981.8010598.5734688.94Kiki Sho9137.046161.932809.65981.810598.634688.94701-01-20Tynia Malone39QuadEast1481.47Tynia Malone948.47339.5038904.912434.6314517.6357145.14Tynia Malone948.47339.538904.912434.6314517.657145.14801-01-20Kiki Sho48SunsetMidWest934.2Grand Total25691.7823795.12110153.553799.0561181.59274621.01Grand Total25691.7823795.12110153.4753799.161181.6274621.01901-01-20Hien Pham13QuadCanada580.971002-01-20Tynia Malone27YanakiNorthWest641.45=APIVOT(Rev,2,4,6,1)vf=1count1102-01-20Hien Pham13SunsetMidWest337.35(2\4) 6 vf=1Crested BeautMajestic BeautQuadSunsetYanakiGrand Total1202-01-20Chantel Mims36SunsetMidWest732.87Chantel Mims75676311302-01-20Tynia Malone44QuadSouth1596.61Hien Pham24567241403-01-20Hien Pham100Crested BeautMidWest1816.5Janis Figueroa12421101503-01-20Chantel Mims7YanakiMexico188.65Kiki Sho52345191603-01-20Hien Pham5YanakiMexico139.75Tynia Malone11773191703-01-20Chantel Mims37QuadMexico1374.05Grand Total16142526221031803-01-20Janis Figueroa525QuadMexico13188.921903-01-20Chantel Mims450SunsetNorthWest6714.56=APIVOT(Rev,2,4,6,2)vf=2minMin of Net RevenueColumn Labels2004-01-20Hien Pham45SunsetCanada916.09(2\4) 6 vf=2Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total2104-01-20Tynia Malone3SunsetWest74.85Chantel Mims215.6209.7312.83639.6726.9526.95Chantel Mims215.6209.7312.83639.6726.9526.952204-01-20Chantel Mims48Crested BeautWest934.2Hien Pham1816.533.95218.4524.9555.924.95Hien Pham1816.533.95218.4524.9555.924.952304-01-20Chantel Mims100Crested BeautMidWest1746.5Janis Figueroa161.734.9585.38174.653039.7534.95Janis Figueroa161.734.9585.38174.653039.7534.952404-01-20Hien Pham2YanakiSouth55.9Kiki Sho249.5305.55305.83311.457.957.9Kiki Sho249.5305.55305.83311.457.957.92504-01-20Kiki Sho25QuadMexico907.16Tynia Malone948.47339.5298.8374.85641.4574.85Tynia Malone948.47339.5298.8374.85641.4574.852604-01-20Chantel Mims8Crested BeautSouth215.6Grand Total161.733.9585.3824.9526.9524.95Grand Total161.733.9585.3824.9526.9524.952704-01-20Kiki Sho34Crested BeautMidWest778.862804-01-20Chantel Mims275YanakiWest4419.59=APIVOT(Rev,2,4,6,3)vf=3max2905-01-20Chantel Mims200QuadEast5976.6(2\4) 6 vf=3Crested BeautMajestic BeautQuadSunsetYanakiGrand Total3005-01-20Hien Pham600YanakiEast9297.75Chantel Mims2724.756291.2514114.387531.788035.6314114.383105-01-20Janis Figueroa36Majestic BeautMidWest1069.47Hien Pham5968.56291.2513816.967574.199297.7513816.963205-01-20Chantel Mims28QuadMidWest1039.82Janis Figueroa161.71069.4713188.925508.53039.7513188.923305-01-20Kiki Sho525YanakiMidWest8437.41Kiki Sho5021.195856.381596.614191.258437.418437.413405-01-20Chantel Mims6Majestic BeautCanada209.7Tynia Malone948.47339.512560.88934.27633.8412560.883505-01-20Kiki Sho9Majestic BeautWest305.55Grand Total5968.56291.2514114.387574.199297.7514114.383605-01-20Hien Pham550QuadMidWest13816.963706-01-20Kiki Sho250SunsetEast4191.25=APIVOT(Rev,2,4,3,)Sum of Units2Column Labels3806-01-20Tynia Malone7QuadCanada298.83(2\4) 3 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total3906-01-20Chantel Mims150Crested BeautCanada2724.75Chantel Mims401321102220058174566Chantel Mims4013211022200581745664006-01-20Tynia Malone43Crested BeautMidWest948.47Hien Pham50029885666011893503Hien Pham500298856660118935034107-01-20Kiki Sho21SunsetNorthWest544.95Janis Figueroa6376774071501277Janis Figueroa63767740715012774207-01-20Hien Pham13QuadWest554.97Kiki Sho560309763316141890Kiki Sho5603097633161418904307-01-20Tynia Malone24SunsetWest529.38Tynia Malone431014711118772512Tynia Malone4310147111187725124407-01-20Tynia Malone15SunsetMexico389.25Grand Total151097541023514364713748Grand Total1510975410235143647137484508-01-20Kiki Sho2YanakiMidWest57.94608-01-20Tynia Malone4SunsetNorthWest99.8=APIVOT(Rev,4,5,3,)Sum of UnitsColumn Labels4708-01-20Kiki Sho42YanakiEast997.82(4\5) 3 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total4808-01-20Chantel Mims500SunsetEast7460.63Crested Beaut1501694112830408891510Crested Beaut1501694112834088915104908-01-20Hien Pham38Majestic BeautEast1161.19Majestic Beaut15381033657259260975Majestic Beaut153810336572592609755008-01-20Tynia Malone48SunsetMexico934.2Quad170241638107827512554454102Quad1702416381078275125544541025108-01-20Chantel Mims29SunsetSouth639.67Sunset55802664497482299853514Sunset558026644974822998535145209-01-20Hien Pham45YanakiCanada1069.09Yanaki458242110655393777763647Yanaki4582421106553937777636475309-01-20Tynia Malone10SunsetCanada239.5Grand Total43520741744325913532328255513748Grand Total435207417443259135323282555137485409-01-20Kiki Sho7QuadWest305.835509-01-20Janis Figueroa6Crested BeautMidWest161.7=APIVOT(Rev,4,5,6,)Sum of Net RevenueColumn Labels5609-01-20Chantel Mims1YanakiWest26.95(4\5) 6 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total5709-01-20Tynia Malone475YanakiNorthWest7633.84Crested Beaut2724.753153.696338.655452.0306184.11838.5625691.78Crested Beaut2724.753153.696338.655452.036184.11838.5625691.785809-01-20Chantel Mims33Crested BeautMexico699.85Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.125909-01-20Janis Figueroa75QuadCanada2457.56Quad5794.927543.4517379.5727417.666908.4833327.4711781.9110153.47Quad5794.927543.4517379.627417.666908.4833327.511781.9110153.476009-01-20Chantel Mims50Majestic BeautNorthWest1348.13Sunset1155.5912777.589997.947512.927533.96639.6714181.453799.05Sunset1155.5912777.69997.947512.927533.96639.6714181.453799.056110-01-20Janis Figueroa7SunsetNorthWest174.65Yanaki1069.0914095.56579.9517433.739222.856298.2412482.261181.59Yanaki1069.0914095.6579.9517433.739222.856298.2412482.261181.596210-01-20Chantel Mims575QuadSouth14114.38Grand Total11268.638731.4734635.6164742.1925264.0753064.2846914.8274621.01Grand Total11268.638731.534635.664742.1925264.0753064.346914.8274621.016310-01-20Janis Figueroa150YanakiEast3039.756411-01-20Kiki Sho300Majestic BeautMidWest5856.386511-01-20Janis Figueroa400SunsetMidWest5508.5dynamic APIVOT interface usingASELECT(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)6611-01-20Chantel Mims32YanakiEast760.24date 1date 2row startrow end6711-01-20Chantel Mims28Crested BeautMexico617.6103-01-2005-01-2012276811-01-20Kiki Sho41Crested BeautWest904.36=APIVOT(H70#,1,2,3,)6911-01-20Chantel Mims425SunsetWest5852.78=ASELECT(Rev,J67,K67,,,{2,4,6})(1\2) 3 vf=0Crested BeautQuadSunsetYanakiGrand Total7012-01-20Chantel Mims6Majestic BeautNorthWest215.7Hien PhamCrested Beaut1816.5Chantel Mims2896.37350.656714.564608.2421569.757112-01-20Hien Pham1Majestic BeautWest33.95Chantel MimsYanaki188.65Hien Pham1816.50916.09195.652928.247212-01-20Chantel Mims40SunsetEast814.3Hien PhamYanaki139.75Janis Figueroa013188.920013188.927312-01-20Hien Pham28YanakiNorthWest689.01Chantel MimsQuad1374.05Kiki Sho778.86907.16001686.027412-01-20Chantel Mims34Crested BeautEast721.06Janis FigueroaQuad13188.92Tynia Malone0074.85074.857512-01-20Hien Pham550SunsetMexico7574.19Chantel MimsSunset6714.56Grand Total5491.6621446.737705.54803.8939447.787613-01-20Chantel Mims7QuadMexico312.83Hien PhamSunset916.097713-01-20Hien Pham9YanakiMexico251.55Tynia MaloneSunset74.857813-01-20Kiki Sho10Crested BeautEast249.5Chantel MimsCrested Beaut934.2top left corner cell format7913-01-20Chantel Mims250Majestic BeautSouth6291.25Chantel MimsCrested Beaut1746.5(rows\columns) values value field=8013-01-20Hien Pham9Majestic BeautSouth323.55Hien PhamYanaki55.9kept it in this shape because if we pivot regular arrays, they do not have headers8113-01-20Tynia Malone375YanakiSouth6242.34Kiki ShoQuad907.168214-01-20Tynia Malone425QuadSouth10921.12Chantel MimsCrested Beaut215.68314-01-20Hien Pham275QuadNorthWest6908.48Kiki ShoCrested Beaut778.868414-01-20Kiki Sho44QuadMexico1596.61Chantel MimsYanaki4419.598514-01-20Janis Figueroa1Majestic BeautNorthWest34.95Chantel MimsQuad5976.68614-01-20Kiki Sho125Crested BeautEast2183.13APIVOT postCell FormulasRangeFormulaH1,H69,L68,H55,H46,H37,H28,H19,H10H1=FORMULATEXT(H2)H2:N8H2=APIVOT(Rev,2,4,6,)H11:N17H11=APIVOT(Rev,2,4,6,1)H20:N26H20=APIVOT(Rev,2,4,6,2)H29:N35H29=APIVOT(Rev,2,4,6,3)H38:N44H38=APIVOT(Rev,2,4,3,)H47:P53H47=APIVOT(Rev,4,5,3,)H56:P62H56=APIVOT(Rev,4,5,6,)J67J67=XMATCH(H67,Rev[Date],1)K67K67=XMATCH(I67,Rev[Date],-1)L69:Q75L69=APIVOT(H70#,1,2,3,)H70:J85H70=ASELECT(Rev,J67,K67,,,{2,4,6})Dynamic array formulas.


----------



## Xlambda (May 16, 2022)

Inspired by MrExcel today's YT (16Mar2022) : Sorted Summary Report WIth Totals In Excel Without A Pivot Table - 2487
2 functions to reproduce Pivot Tables functionality:
1st, *PIVOT*, a *lambda helper function*, does the calculations corresponding to the "inner" array calculations and can be used anywhere as stand-alone function or inside other functions constructions, can work with *ANY function*, also can reproduce functionality of all ...IFS functions, without the range argument limitations of ...IFS functions. 
2nd, *APIVOT*, Array Pivot, *completes PT layout* "cosmetics", does rows/columns labels and grand totals calculations.

*PIVOT(r,c,v,fn)* *Lambda Helper Pivot Table function
r*: rows column of an array or table.
*c*: columns column of an array or table
*v*: values column of an array or table
*fn*: lambda helper function argument: LAMBDA(x,function(x))
*Note: If fn returns more than a single value, those values will be text joined.*

```
=LAMBDA(r,c,v,fn,
    LET(
        d, ",",ur, SORT(UNIQUE(r)),uc, SORT(UNIQUE(c)),
        w, ROWS(ur),l, ROWS(uc),
        MAKEARRAY(w,l,LAMBDA(y,x,
                LET(
                    a, INDEX(ur, y),b, INDEX(uc, x),
                    i, IF((a = r) * (b = c), v, ""),f, FILTER(i, i <> ""),
                    fx, IF(ISERR(SUM(f)), "", IFERROR(fn(f), "")),
                    IF(COUNTA(fx) > 1, TEXTJOIN(d, , fx), fx))))
    )
)
```
*APIVOT(r,c,v,fn,[p],[tc],[tr])* completes the pivot table layout "cosmetics" with labels and grand totals calculations. Calls *PIVOT*
*r,c,v,fn*: same argument as previous function
*[p]*: pivot table name label, if omitted "PT"
*[tc]*: trailing column label, if omitted "GT"
*[tr]*: trailing row label, if omitted "GT"

```
=LAMBDA(r, c, v, fn, [p], [tr], [tc],
    LET(
        ur, SORT(UNIQUE(r)),
        uc, TOROW(SORT(UNIQUE(c))),
        x, VSTACK(IF(p = "", "PT", p), ur, IF(tc = "", "GT", tc)),
        y, VSTACK(uc, PIVOT(r, c, v, fn), PIVOT(, c, v, fn)),
        z, VSTACK(IF(tr = "", "GT", tr), PIVOT(r, , v, fn), PIVOT(, , v, fn)),
        a, HSTACK(x, y, z),
        FILTER(FILTER(a, TAKE(a, 1) <> 0), TAKE(a, , 1) <> 0)
    )
)
```
LHPIVOT.xlsxABCDEFGHIJKLMNOPQRSTUVW1sample array (not table)r,"Product" clm,c,omitted,v,"Sales" clm, fn, sum/count2TeamNameProductSalesc,omitted => only grand totals will be displayed, similar to sumifs/countifs functionality3RedAmandaApple559=APIVOT(D3:D28,,E3:E28,LAMBDA(x,SUM(x)),"Product","Sales","Total")4RedThiagoKiwi40↓↓↓↓=APIVOT(D3:D28,,E3:E28,LAMBDA(x,COUNT(x)),"Product","Count","Total")5RedCarolLemon808ProductSales=SUMIFS(E3:E28,D3:D28,G6:G11)ProductCount=COUNTIFS(D3:D28,O6:O11)6BlueDanielLime200Apple36113611Apple667RedDanielaApple903Kiwi4040Kiwi118BlueEduardoOrange656Lemon31083108Lemon669RedGabrielLemon404Lime25852585Lime6610BlueHelenaLime526Orange28012801Orange6611BlueBernandoOrange291Quince578578Quince1112RedIsabellaApple559Total12723Total2613BlueJuao PedroOrange48514RedJoseLemon354r,"Product" clm, c,"Team" clm, v,"Sales" clm, fn,sum, p,Prd/Team, tc,tr,omitted15BlueStephanieLime270=APIVOT(D3:D28,B3:B28,E3:E28,LAMBDA(x,SUM(x)),"Prd/Team")16RedThiagoApple693Prd/TeamBlueRedGT17RedAmandaOrange235Apple8972714361118BlueBernandoLemon610Kiwi404019RedCarolLime619Lemon15421566310820BlueDanielApple612Lime9961589258521RedDanielaOrange596Orange14321369280122BlueEduardoLemon448Quince57857823RedGabrielLime851GT544572781272324BlueHelenaApple28525RedIsabellaOrange538r,"Name" clm, c,"Product" clm, v,"Sales" clm, fn,average, p,tc,tr,omitted26BlueJuao PedroLemon484=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,AVERAGE(x)))27RedJoseLime119PTAppleKiwiLemonLimeOrangeQuinceGT28BlueStephanieQuince578Amanda55923539729Bernando610291450.530Carol808619713.531Daniel61220040632Daniela903596749.533Eduardo44865655234Gabriel404851627.535Helena285526405.536Isabella559538548.537Jose354119236.538Juao Pedro484485484.539Stephanie27057842440Thiago69340366.541GT601.840518430.8466.8578489.342PIV 1Cell FormulasRangeFormulaG3G3=FORMULATEXT(G5)O4,G26,G15,R5,J5O4=FORMULATEXT(O5)G5:H12G5=APIVOT(D3:D28,,E3:E28,LAMBDA(x,SUM(x)),"Product","Sales","Total")O5:P12O5=APIVOT(D3:D28,,E3:E28,LAMBDA(x,COUNT(x)),"Product","Count","Total")J6:J11J6=SUMIFS(E3:E28,D3:D28,G6:G11)R6:R11R6=COUNTIFS(D3:D28,O6:O11)G16:J23G16=APIVOT(D3:D28,B3:B28,E3:E28,LAMBDA(x,SUM(x)),"Prd/Team")G27:N41G27=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,AVERAGE(x)))Dynamic array formulas.


----------



## Xlambda (May 16, 2022)

*Versatility of PIVOT/APIVOT.*
*Omitting r or c or both arguments* *triggers PIVOT function to calculate Grand Totals row/column and Grand Total general.*
That functionality is used by APIVOT to attach PT Grand Total elements
......y, VSTACK(uc, PIVOT(r, c, v, fn), *PIVOT(, c, v, fn))*, z, VSTACK(IF(tr = "", "GT", tr), *PIVOT(r, , v, fn)*, *PIVOT(, , v, fn)*),.....
LHPIVOT.xlsxABCDEFGHIJKLMNOPQRST1sample array (not table)Functionality PIVOT/APIVOT when r or c or both arguments are omitted2TeamNameProductSales1. c,omitted3RedAmandaApple559r,"Name" clm, c,omitted, v,"Sales" clm, fn,sum4RedThiagoKiwi40=PIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))5RedCarolLemon808↓↓↓↓=APIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))6BlueDanielLime200↓↓↓↓PTGT7RedDanielaApple903794Amanda7942. r,omitted8BlueEduardoOrange656901Bernando901r,omitted, c,"Product" clm, v,"Sales" clm, fn,sum9RedGabrielLemon4041427Carol1427=PIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))10BlueHelenaLime526812Daniel81236114031082585280157811BlueBernandoOrange2911499Daniela149912RedIsabellaApple5591104Eduardo1104=APIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))13BlueJuao PedroOrange4851255Gabriel1255PTAppleKiwiLemonLimeOrangeQuinceGT14RedJoseLemon354811Helena811GT3611403108258528015781272315BlueStephanieLime2701097Isabella109716RedThiagoApple693473Jose4733. r,c, omitted17RedAmandaOrange235969Juao Pedro969=PIVOT(,,E3:E28,LAMBDA(x,SUM(x)))18BlueBernandoLemon610848Stephanie8481272319RedCarolLime619733Thiago73320BlueDanielApple612GT12723=APIVOT(,,E3:E28,LAMBDA(x,SUM(x)))21RedDanielaOrange596PTGT22BlueEduardoLemon448GT1272323RedGabrielLime85124BlueHelenaApple285Actually this versatility is used on purpose by the design of APIVOT to calculate Grand Totals row/column/general25RedIsabellaOrange538and attach them to the final PT layout.26BlueJuao PedroLemon48427RedJoseLime119r,"Name" clm, c,"Product" clm, v,"Sales" clm, fn,sum, p,tc,tr,omitted28BlueStephanieQuince578=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,SUM(x)))29PTAppleKiwiLemonLimeOrangeQuinceGT30Amanda55923579431Bernando61029190132Carol808619142733Daniel61220081234Daniela903596149935Eduardo448656110436Gabriel404851125537Helena28552681138Isabella559538109739Jose35411947340Juao Pedro48448596941Stephanie27057884842Thiago6934073343GT3611403108258528015781272344PIV 2Cell FormulasRangeFormulaG4G4=FORMULATEXT(G7)I5,G28,L20,M17,L12,M9I5=FORMULATEXT(I6)I6:J20I6=APIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))G7:G19G7=PIVOT(C3:C28,,E3:E28,LAMBDA(x,SUM(x)))M10:R10M10=PIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))L13:S14L13=APIVOT(,D3:D28,E3:E28,LAMBDA(x,SUM(x)))M18M18=PIVOT(,,E3:E28,LAMBDA(x,SUM(x)))L21:M22L21=APIVOT(,,E3:E28,LAMBDA(x,SUM(x)))G29:N43G29=APIVOT(C3:C28,D3:D28,E3:E28,LAMBDA(x,SUM(x)))Dynamic array formulas.


----------



## Xlambda (May 18, 2022)

*Functionality PIVOT/APIVOT when values clm "v" arguments is omitted.*
LHPIVOT.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE1sample array (not table)Functionality PIVOT/APIVOT when v arguments is omitted2TeamNameProductSales3RedAmandaApple559=PIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))4RedThiagoKiwi40↓↓=APIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))5RedCarolLemon808↓↓PTAppleKiwiLemonLimeOrangeQuinceGT6BlueDanielLime20000Amanda0007RedDanielaApple90300Bernando0008BlueEduardoOrange65600Carol0009RedGabrielLemon40400Daniel00010BlueHelenaLime52600Daniela00011BlueBernandoOrange29100Eduardo00012RedIsabellaApple55900Gabriel00013BlueJuao PedroOrange48500Helena00014RedJoseLemon35400Isabella00015BlueStephanieLime27000Jose00016RedThiagoApple69300Juao Pedro00017RedAmandaOrange23500Stephanie00018BlueBernandoLemon61000Thiago00019RedCarolLime619GT00000020BlueDanielApple61221RedDanielaOrange596Makes sense when values argument "v" is omitted to get a bunch of 0'sCompared with PT, this is how PT will look when no values clm is selected22BlueEduardoLemon448that with our functions, at least, can be counted.23RedGabrielLime851=APIVOT(C3:C28,D3:D28,,LAMBDA(x,COUNT(x)))Column Labels24BlueHelenaApple285PTAppleKiwiLemonLimeOrangeQuinceGTRow LabelsAppleKiwiLemonLimeOrangeQuinceGrand Total25RedIsabellaOrange538Amanda112Amanda26BlueJuao PedroLemon484Bernando112Bernando27RedJoseLime119Carol112Carol28BlueStephanieQuince578Daniel112Daniel29Daniela112Daniela30Eduardo112Eduardo31Gabriel112Gabriel32Helena112Helena33Isabella112Isabella34Jose112Jose35Juao Pedro112Juao Pedro36Stephanie112Stephanie37Thiago112Thiago38GT616661Grand Total3940Note: When "v" is omitted notice that we have no value for Grand Total general(bottom right corner)41To get the GT general for count we have to choose a column as values "v", if "v" clm has only text values will use as "fn" COUNTA,42if "v" has numeric values we can use as fn both COUNT or COUNTA43=APIVOT(C3:C28,D3:D28,B3:B28,LAMBDA(x,COUNTA(x)))Count of TeamColumn Labels44PTAppleKiwiLemonLimeOrangeQuinceGTRow LabelsAppleKiwiLemonLimeOrangeQuinceGrand Total45Amanda112Amanda11246Bernando112Bernando11247Carol112Carol11248Daniel112Daniel11249Daniela112Daniela11250Eduardo112Eduardo11251Gabriel112Gabriel11252Helena112Helena11253Isabella112Isabella11254Jose112Jose11255Juao Pedro112Juao Pedro11256Stephanie112Stephanie11257Thiago112Thiago11258GT61666126Grand Total6166612659PIV 3Cell FormulasRangeFormulaG3G3=FORMULATEXT(G6)N4,N43,N23N4=FORMULATEXT(N5)N5:U19N5=APIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))G6:L18G6=PIVOT(C3:C28,D3:D28,,LAMBDA(x,SUM(x)))N24:U38N24=APIVOT(C3:C28,D3:D28,,LAMBDA(x,COUNT(x)))N44:U58N44=APIVOT(C3:C28,D3:D28,B3:B28,LAMBDA(x,COUNTA(x)))Dynamic array formulas.


----------



## Xlambda (May 18, 2022)

To visualize the simple *concept of PIVOT* that is capable of creating an *array of arrays* we can simply use as function argument "fn" *LAMBDA(x,x)*
And all this is possible using *a single MAKEARRAY* function construction found in PIVOT. Everything else build around MAKEARRAY in PIVOT is only cosmetics. APIVOT, that calls PIVOT, is only about cosmetics.
In other words, for every intersection row/clm values ("r"/"c") the corresponded matched values array of "v" is extracted. And to these values we can apply *any function that can return a single result or more.*
Using LAMBDA(x,x) as function argument ("fn"), PIVOT, (respectivly APIVOT) will return all matched results of the correspondent "r"/"c" intersection conditions.
*Note*: The default delimiter used in PIVOT as separator between values is ",".
If we want to change it, is the first defined variable "d" after LET in PIVOT.
=LAMBDA(r,c,v,fn,LET(*d,","*,ur,SORT(UNIQUE(r)), …......
LHPIVOT.xlsxABCDEFGHIJKLMNOPQRSTU1sample array (no headers)2=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,x))3Azb1PTabcdGT4Bya5A17,741,4,7,75Czd7B5,3,326,95,2,3,6,3,96Dzc4C3,4,44,477,3,4,4,4,47Cxb3D89,944,8,9,98Axd4GT5,8,3,31,3,4,9,4,94,2,7,4,7,47,4,6,91,5,7,4,3,4,8,2,7,4,4,9,3,6,7,4,4,9,3,99Dya810Bxc2=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,x))11Ayc7PTabcdGT12Czc4Azy,yxz,x,y,y13Czb4By,x,xxz,zy,x,x,z,x,z14Dyb9Cx,z,zz,zzz,x,z,z,z,z15Bxa3Dyy,yzz,y,y,y16Bzd6GTy,y,x,xz,x,z,y,z,yz,x,y,z,y,zz,x,z,zz,y,z,z,x,x,y,x,y,z,z,y,x,z,y,z,z,y,x,zNote: Another advantage of using 17Ayc7lambda helper functions combo PIVOT/APIVOT is 18Czc4that PT interface is not reliable when an array has no headers.19Czb4checking consistency of the results It will consider first row as headers, therefore results will not be accurate.20Dyb9=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,COUNTA(x)))Count of 1Column Labels21Bxa3PTabcdGTRow LabelsabcdGrand Total22Bzd9A1214A21323B3126B312624C3216C321625D1214D121426GT466420Grand Total4564192728APIVOT excluding first row:29=APIVOT(B4:B22,D4:D22,C4:C22,LAMBDA(x,COUNTA(x)))30PTabcdGT31A21332B312633C321634D121435GT45641936PIV 4Cell FormulasRangeFormulaG2,N29,G20,G10G2=FORMULATEXT(G3)G3:L8G3=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,x))G11:L16G11=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,x))G21:L26G21=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,COUNTA(x)))N30:S35N30=APIVOT(B4:B22,D4:D22,C4:C22,LAMBDA(x,COUNTA(x)))Dynamic array formulas.


----------



## Xlambda (May 26, 2022)

*Quirks an Features of PIVOT/APIVOT:*
- PIVOT, being designed only for calculations, can be used as stand-alone function, or can be called by other formulas or functions constructions.
- APIVOT can be used as presentation/visualizing interface of PIVOT.
- Can handle arrays with no headers.
- Being lambda helper functions, *can handle any functions* or formulas constructions.
- Can return *more than one result*.
- All the *arguments* r,c,v *can be/handle array calculations*.
- Last but not least, *no refresh*.
*Simple examples to reflect all these.* To be continued, asap(time), with a tone of real-life complex task solving scenarios. More functions, more techniques still to come.
LHPIVOT.xlsxABCDEFGHIJKLMNOPQRSTUV1sample array (no headers)top 2 values including tiesAPIVOT can handle array calculations for its arguments2fn,LARGE(x,{1,2})all values >=53Azb1=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,LARGE(x,{1,2})))=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,x),"all >=5")4Bya5PTabcdGTall >=5abcdGT5Czd7A17,747,7A7,77,76Dzc4B8,529,69,8B5,5,86,95,5,6,8,97Cxb3C4,44,477,4C778Axd4D89,949,9D89,98,9,99Dya8GT8,89,97,79,79,9GT5,8,5,89,97,77,6,95,7,8,7,9,5,6,7,9,8,910Bxc211Ayc7top 2 values excluding tiesvalues >=512Czc4fn,LARGE(UNIQUE(x),{1,2})=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,UNIQUE(x)),">=5")13Czb4PTabcdGT>=5abcdGT14Dyb9A1747,4A7715Bxa5B8,529,69,8B5,86,95,6,8,916Bzd6C4,3477,4C7717Ayc7D8949,8D898,918Czc4GT8,59,47,49,79,8GT5,8977,6,95,7,8,9,619Czb420Dyb9unique values distribution x,y,z21Bxa8fn,SORT(UNIQUE(x))count values >=522Bzd9=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,SORT(UNIQUE(x))),"xyz distrib")=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,COUNT(x)),"cnt >=5")23xyz distribabcdGTcnt >=5abcdGT24Azyxx,y,zA2225Bx,yxzx,y,zB32526Cx,zzzx,zC1127Dyyzy,zD12328GTx,yx,y,zx,y,zx,zx,y,zGT4223112930sum distribution all a,b,c,dsum distribution for a,b ; c and d cumulated as "others"31=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,SUM(x)),"sum")=APIVOT(B3:B22,IF((D3:D22="a")+(D3:D22="b"),D3:D22,"other"),E3:E22,LAMBDA(x,SUM(x)),"a,b")32sumabcdGTa,babotherGT33A114419A1181934B1821535B18173535C118726C11152636D818430D81843037GT26302826110GT26305411038PIV 5Cell FormulasRangeFormulaG3,G31,G22,O22,O12,O3G3=FORMULATEXT(G4)G4:L9G4=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,LARGE(x,{1,2})))O4:T9O4=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,x),"all >=5")G13:L18G13=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,LARGE(UNIQUE(x),{1,2})))O13:T18O13=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,UNIQUE(x)),">=5")G23:L28G23=APIVOT(B3:B22,D3:D22,C3:C22,LAMBDA(x,SORT(UNIQUE(x))),"xyz distrib")O23:T28O23=APIVOT(B3:B22,D3:D22,IF(E3:E22>=5,E3:E22,""),LAMBDA(x,COUNT(x)),"cnt >=5")N31N31=FORMULATEXT(O32)G32:L37G32=APIVOT(B3:B22,D3:D22,E3:E22,LAMBDA(x,SUM(x)),"sum")O32:S37O32=APIVOT(B3:B22,IF((D3:D22="a")+(D3:D22="b"),D3:D22,"other"),E3:E22,LAMBDA(x,SUM(x)),"a,b")Dynamic array formulas.


----------



## Xlambda (May 29, 2022)

Since APIVOT arguments can take array calculations, *manipulating dates* comes handy.
Check out Leila's PT techniques YT: Properly Handling Date Grouping in Excel Pivot Tables (Change Grouping, Get All dates)
workbook download link: Excel Pivot Table Date Grouping - Xelplus - Leila Gharani 
LHPIVOT.xlsxABCDEFGHIJKLMNOPQ1sample table 600 rows=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)),"Year-Month","Tot Orders")2SalespersonOrder IDOrder dateOrder amount↓=SORT(UNIQUE(S[Salesperson]))3Jack Potter328404-01-214686.243939Year-MonthTot OrdersDV formula4Bob Caldwell328504-01-2117702.126922021-(01)-Jan567729.4435Amy Trefl5Bob Caldwell328605-01-218563.4691322021-(02)-Feb691142.9563Anthony Grosse6Taj Shand328706-01-2120481.330252021-(03)-Mar658502.6592Archer Lamble7Jack Potter328809-01-2115780.993342021-(04)-Apr495310.5648Bob Caldwell8Justine Hoffer328911-01-217270.7949362021-(05)-May458611.6175Don Johnson9Sheridan Smith329011-01-215840.1692972021-(06)-Jun373276.693Hudson Hollinworth10Taj Shand329111-01-2115376.807142021-(07)-Jul591065.3804Hudson Onslow11Archer Lamble329212-01-2123132.982032021-(08)-Aug457049.7136Jack Potter12Hudson Hollinworth329312-01-2119249.777662021-(09)-Sep766123.202Justine Hoffer13Hudson Onslow329412-01-2118854.760982021-(10)-Oct606950.1752Kayla Woodcock14Justine Hoffer329512-01-2121544.813062021-(11)-Nov690283.5764Lily Code15Taj Shand329613-01-2123344.867172021-(12)-Dec813876.6413Peter Chowdhry16Don Johnson329715-01-2115420.931392022-(01)-Jan730249.9767Sheridan Smith17Amy Trefl329816-01-2115300.264472022-(02)-Feb604319.1448Sophia Hinton18Lily Code329918-01-218091.068995GT8504491.745Taj Shand19Hudson Onslow330018-01-2116146.7069620Bob Caldwell330118-01-215858.81749521Kayla Woodcock330219-01-2118023.42206=APIVOT(WEEKDAY(S[Order date],2)&"-"&TEXT(S[Order date],"ddd"),,S[Order amount],LAMBDA(x,SUM(x)),"SUM/wdays")22Sheridan Smith330320-01-2121212.95523SUM/wdaysGT23Hudson Hollinworth330421-01-216270.7275471-Mon2357492.283Selection24Archer Lamble330521-01-2111684.544992-Tue1259545.731DV Salesperson25Sophia Hinton330621-01-2123250.868073-Wed1133082.268Archer Lamble26Hudson Onslow330721-01-215584.4120334-Thu1137252.179Lily Code27Hudson Hollinworth330821-01-2118973.509985-Fri1257000.39228Kayla Woodcock330923-01-2115544.879426-Sat1360118.89329Sophia Hinton331025-01-219941.138917GT8504491.74530Lily Code331125-01-215418.41454731Hudson Onslow331226-01-2116683.98206=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),IF(ISNUMBER(XMATCH(S[Salesperson],J25:J26)),S[Salesperson],"Ꝋthers"),S[Order amount],LAMBDA(x,SUM(x)),"SUM Selection")32Amy Trefl331328-01-2121285.61157SUM SelectionArcher LambleLily CodeꝊthersGT=IFERROR(G33:G47+H33:H47,"")33Bob Caldwell331428-01-2117873.080372021-(01)-Jan45797.3006413509.48354508422.6593567729.443559306.7841934Amy Trefl331529-01-2118233.579372021-(02)-Feb34954.9209231727.6317624460.4037691142.956366682.5526235Amy Trefl331629-01-2120485.994422021-(03)-Mar52455.9143839902.49664566144.2481658502.659292358.4110236Archer Lamble331729-01-2110979.773622021-(04)-Apr13984.29783481326.267495310.564837Hudson Hollinworth331829-01-2116994.455542021-(05)-May9768.43214243178.77407405664.4112458611.617552947.2062238Justine Hoffer331930-01-219521.6010812021-(06)-Jun22514.2434110012.9606340749.4889373276.69332527.2040139Bob Caldwell332030-01-219560.2603412021-(07)-Jul34064.86321101145.9112455854.606591065.3804135210.774440Sheridan Smith332130-01-2114020.608022021-(08)-Aug14390.2614958419.51281384239.9393457049.713672809.774341Bob Caldwell332230-01-2113538.699122021-(09)-Sep38575.42867727547.7733766123.20242Bob Caldwell332301-02-2121596.070582021-(10)-Oct10239.0428750764.67789545946.4545606950.175261003.7207743Sheridan Smith332401-02-218228.7962332021-(11)-Nov70241.5688222955.95445597086.0531690283.576493197.5232744Kayla Woodcock332501-02-2124965.015882021-(12)-Dec50230.87472126931.199636714.5677813876.6413177162.073745Don Johnson332601-02-2114049.725322022-(01)-Jan30490.9917559606.79675640152.1882730249.976790097.7885146Hudson Hollinworth332701-02-2113880.847072022-(02)-Feb25638.8563937906.4995540773.7889604319.144863545.3558947Justine Hoffer332801-02-2123031.95227GT439362.6994610046.19597455082.8498504491.7451049408.89548Anthony Grosse332901-02-2122969.8955849Jack Potter333002-02-2113545.1105850Bob Caldwell333102-02-216750.206713Omega char=UNICODE(H51)51Anthony Grosse333202-02-2111978.34044Ꝋ4282652Don Johnson333302-02-2114838.46588PIV 6Cell FormulasRangeFormulaF1,J2F1=FORMULATEXT(F3)F3:G18F3=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)),"Year-Month","Tot Orders")J4:J18J4=SORT(UNIQUE(S[Salesperson]))F21,I50,L32,F31F21=FORMULATEXT(F22)F22:G29F22=APIVOT(WEEKDAY(S[Order date],2)&"-"&TEXT(S[Order date],"ddd"),,S[Order amount],LAMBDA(x,SUM(x)),"SUM/wdays")F32:J47F32=APIVOT(TEXT(S[Order date],"yyyy-(mm)-mmm"),IF(ISNUMBER(XMATCH(S[Salesperson],J25:J26)),S[Salesperson],"Ꝋthers"),S[Order amount],LAMBDA(x,SUM(x)),"SUM Selection")L33:L47L33=IFERROR(G33:G47+H33:H47,"")I51I51=UNICODE(H51)Dynamic array formulas.Cells with Data ValidationCellAllowCriteriaJ25:J29List=$J$4#


----------



## Xlambda (May 29, 2022)

To solve Quarter grouping challenge here is *QTR function*. Capable of dealing also with *fiscal quarters/years*, not only with calendar ones.
Start month (s), of fiscal year by country can be different. Most common s's are 4,7,10.  
The function uses by default s=4 (April) To change this value, change the first variable "s" , first variable declared after LET.
*QTR(a,[qt])*
*a*: dates array
*[qt]*: quarter type argument, text, could be one of these values:
*"q"*: calendar quarter
*"qy"*: calendar quarter and year, (year will be listed first for sorting versatility) ; format ex.: 2022 Q3
*"fq"*: fiscal quarter : format ex.: FQ4
*"fy"*: fiscal year ; format ex.: *FY 2023* (represents *fiscal year 2022-2023*)
*"fqy"*: fiscal quarter and year, (year will be listed first for sorting versatility) ; format ex.: FY 2022 Q2
- if qt is omitted, function will calculate "qy", if qt<> above values function returns #NA() error

```
=LAMBDA(a, [qt],
    LET(
        s, 4,
        m, MONTH(a),
        y, YEAR(a),
        q, "Q" & MONTH(m & 0),
        qy, y & " " & q,
        fq, "Q" & MONTH(MOD(m - s, 12) + 1 & 0),
        fy, "FY " & y + (m >= s),
        fqy, fy & " " & fq,
        SWITCH(qt, 0, qy, "q", q, "qy", qy, "fq", "F" & fq, "fy", fy, "fqy", fqy)
    )
)
```
LHPIVOT.xlsxABCDEFGHIJKLMNOPQ1QTR(a,[qt]). The function.2wrong argument3=DATE(2021,SEQUENCE(24),1)qt,omittedqt,"q"qt,"qy"qt,"fq"qt,"fy"qt,"fqy"qt,"fyq"4↓↓↓=QTR(B5#)=QTR(B5#,"q")=QTR(B5#,"qy")=QTR(B5#,"fq")=QTR(B5#,"fy")=QTR(B5#,"fqy")=QTR(B5#,"fyq")501-01-212021 Q1Q12021 Q1FQ4FY 2021FY 2021 Q4#N/A601-02-212021 Q1Q12021 Q1FQ4FY 2021FY 2021 Q4701-03-212021 Q1Q12021 Q1FQ4FY 2021FY 2021 Q4801-04-212021 Q2Q22021 Q2FQ1FY 2022FY 2022 Q1901-05-212021 Q2Q22021 Q2FQ1FY 2022FY 2022 Q11001-06-212021 Q2Q22021 Q2FQ1FY 2022FY 2022 Q11101-07-212021 Q3Q32021 Q3FQ2FY 2022FY 2022 Q21201-08-212021 Q3Q32021 Q3FQ2FY 2022FY 2022 Q21301-09-212021 Q3Q32021 Q3FQ2FY 2022FY 2022 Q21401-10-212021 Q4Q42021 Q4FQ3FY 2022FY 2022 Q31501-11-212021 Q4Q42021 Q4FQ3FY 2022FY 2022 Q31601-12-212021 Q4Q42021 Q4FQ3FY 2022FY 2022 Q31701-01-222022 Q1Q12022 Q1FQ4FY 2022FY 2022 Q41801-02-222022 Q1Q12022 Q1FQ4FY 2022FY 2022 Q41901-03-222022 Q1Q12022 Q1FQ4FY 2022FY 2022 Q42001-04-222022 Q2Q22022 Q2FQ1FY 2023FY 2023 Q12101-05-222022 Q2Q22022 Q2FQ1FY 2023FY 2023 Q12201-06-222022 Q2Q22022 Q2FQ1FY 2023FY 2023 Q12301-07-222022 Q3Q32022 Q3FQ2FY 2023FY 2023 Q22401-08-222022 Q3Q32022 Q3FQ2FY 2023FY 2023 Q22501-09-222022 Q3Q32022 Q3FQ2FY 2023FY 2023 Q22601-10-222022 Q4Q42022 Q4FQ3FY 2023FY 2023 Q32701-11-222022 Q4Q42022 Q4FQ3FY 2023FY 2023 Q32801-12-222022 Q4Q42022 Q4FQ3FY 2023FY 2023 Q329PIV 7Cell FormulasRangeFormulaA3A3=FORMULATEXT(B5)F4,H4,J4,L4,N4,P4,D4F4=FORMULATEXT(F5)B5:B28B5=DATE(2021,SEQUENCE(24),1)D5:D28D5=QTR(B5#)F5:F28F5=QTR(B5#,"q")H5:H28H5=QTR(B5#,"qy")J5:J28J5=QTR(B5#,"fq")L5:L28L5=QTR(B5#,"fy")N5:N28N5=QTR(B5#,"fqy")P5P5=QTR(B5#,"fyq")Dynamic array formulas.


----------



## Xlambda (May 29, 2022)

*Fiscal/Calendar year/quarter reports with APIVOT*.
LHPIVOT.xlsxABCDEFGHIJKLMN1sample table 600 rows=APIVOT(QTR(S[Order date]),,S[Order amount],LAMBDA(x,SUM(x)))2SalespersonOrder IDOrder dateOrder amount↓↓↓=APIVOT(QTR(S[Order date],"q"),,S[Order amount],LAMBDA(x,SUM(x)))3Jack Potter328404-01-214686.243939↓↓↓↓↓↓Pivot table check4Bob Caldwell328504-01-2117702.12692PTGTPTGTRow LabelsSum of Order amount5Bob Caldwell328605-01-218563.4691322021 Q11917375.059Q13251944.18Qtr13251944.186Taj Shand328706-01-2120481.330252021 Q21327198.875Q21327198.875Qtr21327198.8757Jack Potter328809-01-2115780.993342021 Q31814238.296Q31814238.296Qtr31814238.2968Justine Hoffer328911-01-217270.7949362021 Q42111110.393Q42111110.393Qtr42111110.3939Sheridan Smith329011-01-215840.1692972022 Q11334569.122GT8504491.745Grand Total8504491.74510Taj Shand329111-01-2115376.80714GT8504491.74511Archer Lamble329212-01-2123132.9820312Hudson Hollinworth329312-01-2119249.77766=APIVOT(QTR(S[Order date],"fq"),,S[Order amount],LAMBDA(x,SUM(x)))13Hudson Onslow329412-01-2118854.76098PTGT14Justine Hoffer329512-01-2121544.81306FQ11327198.875=APIVOT(QTR(S[Order date],"fy"),,S[Order amount],LAMBDA(x,SUM(x)))15Taj Shand329613-01-2123344.86717FQ21814238.296PTGT16Don Johnson329715-01-2115420.93139FQ32111110.393FY 20211917375.05917Amy Trefl329816-01-2115300.26447FQ43251944.18FY 20226587116.686Pivot Table check (values  F4:G10)18Lily Code329918-01-218091.068995GT8504491.745GT8504491.745Row LabelsSum of Order amount19Hudson Onslow330018-01-2116146.70696202120Bob Caldwell330118-01-215858.817495=APIVOT(QTR(S[Order date],"fqy"),,S[Order amount],LAMBDA(x,SUM(x)))Qtr11917375.05921Kayla Woodcock330219-01-2118023.42206PTGTQtr21327198.87522Sheridan Smith330320-01-2121212.95523FY 2021 Q41917375.059Qtr31814238.29623Hudson Hollinworth330421-01-216270.727547FY 2022 Q11327198.875Qtr42111110.39324Archer Lamble330521-01-2111684.54499FY 2022 Q21814238.296202225Sophia Hinton330621-01-2123250.86807FY 2022 Q32111110.393Qtr11334569.12226Hudson Onslow330721-01-215584.412033FY 2022 Q41334569.122Grand Total8504491.74527Hudson Hollinworth330821-01-2118973.50998GT8504491.74528Kayla Woodcock330923-01-2115544.8794229Sophia Hinton331025-01-219941.138917Checking quarter values summing correspondent month30Lily Code331125-01-215418.414547=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)))31Hudson Onslow331226-01-2116683.98206PTGTPivot table check32Amy Trefl331328-01-2121285.6115721-(01)-Jan567729.4435Row LabelsSum of Order amount33Bob Caldwell331428-01-2117873.0803721-(02)-Feb691142.9563=SUM(G32:G34)=G5202134Amy Trefl331529-01-2118233.5793721-(03)-Mar658502.6592TRUEJan567729.443535Amy Trefl331629-01-2120485.9944221-(04)-Apr495310.5648Feb691142.956336Archer Lamble331729-01-2110979.7736221-(05)-May458611.6175=SUM(G35:G37)=G6Mar658502.659237Hudson Hollinworth331829-01-2116994.4555421-(06)-Jun373276.693TRUEApr495310.564838Justine Hoffer331930-01-219521.60108121-(07)-Jul591065.3804May458611.617539Bob Caldwell332030-01-219560.26034121-(08)-Aug457049.7136=SUM(G38:G40)=G7Jun373276.69340Sheridan Smith332130-01-2114020.6080221-(09)-Sep766123.202TRUEJul591065.380441Bob Caldwell332230-01-2113538.6991221-(10)-Oct606950.1752Aug457049.713642Bob Caldwell332301-02-2121596.0705821-(11)-Nov690283.5764=SUM(G41:G43)=G8Sep766123.20243Sheridan Smith332401-02-218228.79623321-(12)-Dec813876.6413TRUEOct606950.175244Kayla Woodcock332501-02-2124965.0158822-(01)-Jan730249.9767=SUM(G44:G45)=G9Nov690283.576445Don Johnson332601-02-2114049.7253222-(02)-Feb604319.1448TRUEDec813876.641346Hudson Hollinworth332701-02-2113880.84707GT8504491.745202247Justine Hoffer332801-02-2123031.95227Jan730249.976748Anthony Grosse332901-02-2122969.89558Feb604319.144849Jack Potter333002-02-2113545.11058table rowsGrand Total8504491.74550Bob Caldwell333102-02-216750.206713=ROWS(S)51Anthony Grosse333202-02-2111978.3404460052Don Johnson333302-02-2114838.46588PIV 8Cell FormulasRangeFormulaF1F1=FORMULATEXT(F4)I2I2=FORMULATEXT(I4)F4:G10F4=APIVOT(QTR(S[Order date]),,S[Order amount],LAMBDA(x,SUM(x)))I4:J9I4=APIVOT(QTR(S[Order date],"q"),,S[Order amount],LAMBDA(x,SUM(x)))F12,F50,I44,I42,I39,I36,I33,F30,F20,I14F12=FORMULATEXT(F13)F13:G18F13=APIVOT(QTR(S[Order date],"fq"),,S[Order amount],LAMBDA(x,SUM(x)))I15:J18I15=APIVOT(QTR(S[Order date],"fy"),,S[Order amount],LAMBDA(x,SUM(x)))F21:G27F21=APIVOT(QTR(S[Order date],"fqy"),,S[Order amount],LAMBDA(x,SUM(x)))F31:G46F31=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),,S[Order amount],LAMBDA(x,SUM(x)))I34I34=SUM(G32:G34)=G5I37I37=SUM(G35:G37)=G6I40I40=SUM(G38:G40)=G7I43I43=SUM(G41:G43)=G8I45I45=SUM(G44:G45)=G9F51F51=ROWS(S)Dynamic array formulas.


----------



## Xlambda (May 29, 2022)

*Task: Report top/last 3 Order amounts values per each month for each Salesperson*.
LHPIVOT.xlsxABCDEFGHIJKLMNOPQ1sample table 600 rows2SalespersonOrder IDOrder dateOrder amount3Jack Potter328404-01-214686.2439394Bob Caldwell328504-01-2117702.12692=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,LARGE(INT(x),SEQUENCE(3))),"Top 3/mnth","top 3","top 3 all time")5Bob Caldwell328605-01-218563.469132Top 3/mnthtop 36Taj Shand328706-01-2120481.3302521-(01)-January23344, 23250, 231327Jack Potter328809-01-2115780.9933421-(02)-February24965, 24304, 231268Justine Hoffer328911-01-217270.79493621-(03)-March24517, 24219, 238429Sheridan Smith329011-01-215840.16929721-(04)-April24585, 23927, 2385510Taj Shand329111-01-2115376.8071421-(05)-May23970, 22518, 1951411Archer Lamble329212-01-2123132.9820321-(06)-June24594, 24413, 2269712Hudson Hollinworth329312-01-2119249.7776621-(07)-July24720, 24162, 2394213Hudson Onslow329412-01-2118854.7609821-(08)-August24100, 24004, 2360214Justine Hoffer329512-01-2121544.8130621-(09)-September23455, 23261, 2322715Taj Shand329613-01-2123344.8671721-(10)-October24939, 23697, 2365916Don Johnson329715-01-2115420.9313921-(11)-November24714, 24282, 2424517Amy Trefl329816-01-2115300.2644721-(12)-December23874, 23868, 2351418Lily Code329918-01-218091.06899522-(01)-January24782, 24197, 2391619Hudson Onslow330018-01-2116146.7069622-(02)-February24999, 24097, 2344520Bob Caldwell330118-01-215858.817495top 3 all time24999, 24965, 2493921Kayla Woodcock330219-01-2118023.4220622Sheridan Smith330320-01-2121212.9552323Hudson Hollinworth330421-01-216270.727547=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,SMALL(INT(x),SEQUENCE(3))),"Last 3/mnth","last 3","last 3 all time")24Archer Lamble330521-01-2111684.54499Last 3/mnthlast 325Sophia Hinton330621-01-2123250.8680721-(01)-January4686, 5418, 558426Hudson Onslow330721-01-215584.41203321-(02)-February4070, 4580, 490127Hudson Hollinworth330821-01-2118973.5099821-(03)-March4113, 4426, 450228Kayla Woodcock330923-01-2115544.8794221-(04)-April4589, 5057, 517429Sophia Hinton331025-01-219941.13891721-(05)-May4304, 4745, 487330Lily Code331125-01-215418.41454721-(06)-June4776, 5091, 523631Hudson Onslow331226-01-2116683.9820621-(07)-July5204, 6209, 674332Amy Trefl331328-01-2121285.6115721-(08)-August4731, 5012, 538533Bob Caldwell331428-01-2117873.0803721-(09)-September4428, 4430, 478334Amy Trefl331529-01-2118233.5793721-(10)-October4018, 4841, 510035Amy Trefl331629-01-2120485.9944221-(11)-November4319, 4546, 504936Archer Lamble331729-01-2110979.7736221-(12)-December4259, 4279, 439637Hudson Hollinworth331829-01-2116994.4555422-(01)-January4024, 4113, 446438Justine Hoffer331930-01-219521.60108122-(02)-February4189, 4398, 706039Bob Caldwell332030-01-219560.260341last 3 all time4018, 4024, 407040Sheridan Smith332130-01-2114020.6080241Bob Caldwell332230-01-2113538.69912PIV 9Cell FormulasRangeFormulaF4,F23F4=FORMULATEXT(F5)F5:G20F5=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,LARGE(INT(x),SEQUENCE(3))),"Top 3/mnth","top 3","top 3 all time")F24:G39F24=APIVOT(TEXT(S[Order date],"yy-(mm)-mmmm"),,S[Order amount],LAMBDA(x,SMALL(INT(x),SEQUENCE(3))),"Last 3/mnth","last 3","last 3 all time")Dynamic array formulas.


----------



## Xlambda (May 29, 2022)

*Task 1: Total nr. of Orders registered/Salesperson entire period.
Task 2: How many Salespersons registered at least one order each month of every year.
Task 3: What Salespersons did not register any Order per month and year.*
LHPIVOT.xlsxABCDEFGHIJKLMNOPQR1sample table 600 rows2SalespersonOrder IDOrder dateOrder amount3Jack Potter328404-01-214686.243939Task 14Bob Caldwell328504-01-2117702.12692=APIVOT(S[Salesperson],,S[Order amount],LAMBDA(x,COUNT(x)))5Bob Caldwell328605-01-218563.469132↓↓↓6Taj Shand328706-01-2120481.33025↓↓↓Task 37Jack Potter328809-01-2115780.99334PTGT=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,FILTER(F8:F22,ISNA(XMATCH(F8:F22,SORT(UNIQUE(x)))))))8Justine Hoffer328911-01-217270.794936Amy Trefl35PTGT9Sheridan Smith329011-01-215840.169297Anthony Grosse4321 (01) JanAnthony Grosse, Peter Chowdhry10Taj Shand329111-01-2115376.80714Archer Lamble3421 (02) Feb11Archer Lamble329212-01-2123132.98203Bob Caldwell4221 (03) Mar12Hudson Hollinworth329312-01-2119249.77766Don Johnson4621 (04) AprArcher Lamble, Sophia Hinton13Hudson Onslow329412-01-2118854.76098Hudson Hollinworth4021 (05) MaySophia Hinton14Justine Hoffer329512-01-2121544.81306Hudson Onslow3421 (06) JunJustine Hoffer15Taj Shand329613-01-2123344.86717Jack Potter2921 (07) JulJack Potter16Don Johnson329715-01-2115420.93139Justine Hoffer5521 (08) AugAmy Trefl17Amy Trefl329816-01-2115300.26447Kayla Woodcock3921 (09) SepLily Code18Lily Code329918-01-218091.068995Lily Code4121 (10) Oct19Hudson Onslow330018-01-2116146.70696Peter Chowdhry3521 (11) NovHudson Onslow20Bob Caldwell330118-01-215858.817495Sheridan Smith4321 (12) Dec21Kayla Woodcock330219-01-2118023.42206Sophia Hinton3422 (01) Jan22Sheridan Smith330320-01-2121212.95523Taj Shand5022 (02) FebKayla Woodcock, Peter Chowdhry23Hudson Hollinworth330421-01-216270.727547GT600GT24Archer Lamble330521-01-2111684.5449925Sophia Hinton330621-01-2123250.8680726Hudson Onslow330721-01-215584.412033Task 227Hudson Hollinworth330821-01-2118973.50998=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,COUNTA(SORT(UNIQUE(x)))))28Kayla Woodcock330923-01-2115544.87942PTGT29Sophia Hinton331025-01-219941.13891721 (01) Jan1330Lily Code331125-01-215418.41454721 (02) Feb1531Hudson Onslow331226-01-2116683.9820621 (03) Mar1532Amy Trefl331328-01-2121285.6115721 (04) Apr1333Bob Caldwell331428-01-2117873.0803721 (05) May1434Amy Trefl331529-01-2118233.5793721 (06) Jun1435Amy Trefl331629-01-2120485.9944221 (07) Jul1436Archer Lamble331729-01-2110979.7736221 (08) Aug1437Hudson Hollinworth331829-01-2116994.4555421 (09) Sep1438Justine Hoffer331930-01-219521.60108121 (10) Oct1539Bob Caldwell332030-01-219560.26034121 (11) Nov1440Sheridan Smith332130-01-2114020.6080221 (12) Dec1541Bob Caldwell332230-01-2113538.6991222 (01) Jan1542Bob Caldwell332301-02-2121596.0705822 (02) Feb1343Sheridan Smith332401-02-218228.796233GT1544Kayla Woodcock332501-02-2124965.0158845Don Johnson332601-02-2114049.72532PIV 10Cell FormulasRangeFormulaF4F4=FORMULATEXT(F7)F7:G23F7=APIVOT(S[Salesperson],,S[Order amount],LAMBDA(x,COUNT(x)))I7,F27I7=FORMULATEXT(I8)I8:J23I8=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,FILTER(F8:F22,ISNA(XMATCH(F8:F22,SORT(UNIQUE(x)))))))F28:G43F28=APIVOT(TEXT(S[Order date],"yy (mm) mmm"),,S[Salesperson],LAMBDA(x,COUNTA(SORT(UNIQUE(x)))))Dynamic array formulas.


----------



## Xlambda (Apr 11, 2021)

APIVOT array pivot, reproduces basic functionality of Pivot Table, with a single cell lambda, for any array, regular, dynamic, table. calls AAGGREGATE , APPEND2V , APPEND2H
Basic functionality means: as in PT interface we can select rows values, column values, values, value fields, only sum, count, min and max
- disadvantages: was not designed to compete with mighty PT, does not have grouping functionality as PT does, though, could be added but the formulas can get discouraging huge.
- advantages: no table format needed, no refresh needed, so if we filter a large table for some complex criteria in a simple dynamic array, this lambda can be handy.
Other functions on minisheet ASELECT
*Note:* Sample data, courtesy of Mike Girvin ExcelIsFun

```
=LAMBDA(a,rw,cl,vl,vf,
    LET(g,"Grand Total",fn,IF(vf=0,0,vf),q,SEQUENCE(,COLUMNS(a)),c,INDEX(a,,cl),r,INDEX(a,,rw),v,INDEX(a,,vl),uc,SORT(UNIQUE(c)),ur,SORT(UNIQUE(r)),tc,TRANSPOSE(uc),f,IF(AND(ISNUMBER(v)),fn,1),
       ar,SWITCH(f,0,SUMIFS(v,r,ur,c,tc),2,MINIFS(v,r,ur,c,tc),3,MAXIFS(v,r,ur,c,tc),1,COUNTIFS(r,ur,c,tc)),fg,SWITCH(f,2,0,3,1,4,7,5),
       rc,AAGGREGATE(ar,fg,),br,TRANSPOSE(AAGGREGATE(TRANSPOSE(ar),fg,)),
       w,ROWS(ar)+1,u,COLUMNS(ar)+2,sr,SEQUENCE(w),sc,SEQUENCE(,u)-1,lc,IF(sr=w,g,INDEX(ur,sr)),tr,SWITCH(sc,0,"("&rw&"\"&cl&")"&" "&vl&" vf="&fn,u-1,g,INDEX(tc,sc)),
       aa,APPEND2V(APPEND2H(ar,rc,),br,),ab,IF(aa="",SWITCH(f,2,MIN(br,rc),3,MAX(br,rc),SUM(rc)),aa),ac,APPEND2V(tr,APPEND2H(lc,ab,),),
       IFS(ISNA(XMATCH(fn,{0,1,2,3})),"check function nr.",NOT(AND(ISNUMBER(XMATCH(CHOOSE({1,2,3},rw,cl,vl),q)))),"check selection",TRUE,ac)
    )
)
```
LAMBDA 7.0.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1sample data, courtesy of Mike GirvinExcelIsFun=APIVOT(Rev,2,4,6,)vf=0sumSum of Net RevenueColumn Labels2DateSales RepUnitsProductRegionNet Revenue(2\4) 6 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total301-01-20Tynia Malone500QuadMidWest12560.88Chantel Mims7659.578379.3328169.7129746.5913486.9687442.16Chantel Mims7659.578379.3328169.7129746.61348787442.16401-01-20Kiki Sho38YanakiMidWest902.79Hien Pham7785.007809.9422079.839952.8819538.6867166.33Hien Pham77857809.9422079.839952.8819538.767166.33501-01-20Hien Pham500YanakiMidWest8035.63Janis Figueroa161.701104.4218189.425683.153039.7528178.44Janis Figueroa161.71104.4218189.425683.153039.7528178.44601-01-20Chantel Mims500YanakiWest8035.63Kiki Sho9137.046161.932809.605981.8010598.5734688.94Kiki Sho9137.046161.932809.65981.810598.634688.94701-01-20Tynia Malone39QuadEast1481.47Tynia Malone948.47339.5038904.912434.6314517.6357145.14Tynia Malone948.47339.538904.912434.6314517.657145.14801-01-20Kiki Sho48SunsetMidWest934.2Grand Total25691.7823795.12110153.553799.0561181.59274621.01Grand Total25691.7823795.12110153.4753799.161181.6274621.01901-01-20Hien Pham13QuadCanada580.971002-01-20Tynia Malone27YanakiNorthWest641.45=APIVOT(Rev,2,4,6,1)vf=1count1102-01-20Hien Pham13SunsetMidWest337.35(2\4) 6 vf=1Crested BeautMajestic BeautQuadSunsetYanakiGrand Total1202-01-20Chantel Mims36SunsetMidWest732.87Chantel Mims75676311302-01-20Tynia Malone44QuadSouth1596.61Hien Pham24567241403-01-20Hien Pham100Crested BeautMidWest1816.5Janis Figueroa12421101503-01-20Chantel Mims7YanakiMexico188.65Kiki Sho52345191603-01-20Hien Pham5YanakiMexico139.75Tynia Malone11773191703-01-20Chantel Mims37QuadMexico1374.05Grand Total16142526221031803-01-20Janis Figueroa525QuadMexico13188.921903-01-20Chantel Mims450SunsetNorthWest6714.56=APIVOT(Rev,2,4,6,2)vf=2minMin of Net RevenueColumn Labels2004-01-20Hien Pham45SunsetCanada916.09(2\4) 6 vf=2Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total2104-01-20Tynia Malone3SunsetWest74.85Chantel Mims215.6209.7312.83639.6726.9526.95Chantel Mims215.6209.7312.83639.6726.9526.952204-01-20Chantel Mims48Crested BeautWest934.2Hien Pham1816.533.95218.4524.9555.924.95Hien Pham1816.533.95218.4524.9555.924.952304-01-20Chantel Mims100Crested BeautMidWest1746.5Janis Figueroa161.734.9585.38174.653039.7534.95Janis Figueroa161.734.9585.38174.653039.7534.952404-01-20Hien Pham2YanakiSouth55.9Kiki Sho249.5305.55305.83311.457.957.9Kiki Sho249.5305.55305.83311.457.957.92504-01-20Kiki Sho25QuadMexico907.16Tynia Malone948.47339.5298.8374.85641.4574.85Tynia Malone948.47339.5298.8374.85641.4574.852604-01-20Chantel Mims8Crested BeautSouth215.6Grand Total161.733.9585.3824.9526.9524.95Grand Total161.733.9585.3824.9526.9524.952704-01-20Kiki Sho34Crested BeautMidWest778.862804-01-20Chantel Mims275YanakiWest4419.59=APIVOT(Rev,2,4,6,3)vf=3max2905-01-20Chantel Mims200QuadEast5976.6(2\4) 6 vf=3Crested BeautMajestic BeautQuadSunsetYanakiGrand Total3005-01-20Hien Pham600YanakiEast9297.75Chantel Mims2724.756291.2514114.387531.788035.6314114.383105-01-20Janis Figueroa36Majestic BeautMidWest1069.47Hien Pham5968.56291.2513816.967574.199297.7513816.963205-01-20Chantel Mims28QuadMidWest1039.82Janis Figueroa161.71069.4713188.925508.53039.7513188.923305-01-20Kiki Sho525YanakiMidWest8437.41Kiki Sho5021.195856.381596.614191.258437.418437.413405-01-20Chantel Mims6Majestic BeautCanada209.7Tynia Malone948.47339.512560.88934.27633.8412560.883505-01-20Kiki Sho9Majestic BeautWest305.55Grand Total5968.56291.2514114.387574.199297.7514114.383605-01-20Hien Pham550QuadMidWest13816.963706-01-20Kiki Sho250SunsetEast4191.25=APIVOT(Rev,2,4,3,)Sum of Units2Column Labels3806-01-20Tynia Malone7QuadCanada298.83(2\4) 3 vf=0Crested BeautMajestic BeautQuadSunsetYanakiGrand TotalRow LabelsCrested BeautMajestic BeautQuadSunsetYanakiGrand Total3906-01-20Chantel Mims150Crested BeautCanada2724.75Chantel Mims401321102220058174566Chantel Mims4013211022200581745664006-01-20Tynia Malone43Crested BeautMidWest948.47Hien Pham50029885666011893503Hien Pham500298856660118935034107-01-20Kiki Sho21SunsetNorthWest544.95Janis Figueroa6376774071501277Janis Figueroa63767740715012774207-01-20Hien Pham13QuadWest554.97Kiki Sho560309763316141890Kiki Sho5603097633161418904307-01-20Tynia Malone24SunsetWest529.38Tynia Malone431014711118772512Tynia Malone4310147111187725124407-01-20Tynia Malone15SunsetMexico389.25Grand Total151097541023514364713748Grand Total1510975410235143647137484508-01-20Kiki Sho2YanakiMidWest57.94608-01-20Tynia Malone4SunsetNorthWest99.8=APIVOT(Rev,4,5,3,)Sum of UnitsColumn Labels4708-01-20Kiki Sho42YanakiEast997.82(4\5) 3 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total4808-01-20Chantel Mims500SunsetEast7460.63Crested Beaut1501694112830408891510Crested Beaut1501694112834088915104908-01-20Hien Pham38Majestic BeautEast1161.19Majestic Beaut15381033657259260975Majestic Beaut153810336572592609755008-01-20Tynia Malone48SunsetMexico934.2Quad170241638107827512554454102Quad1702416381078275125544541025108-01-20Chantel Mims29SunsetSouth639.67Sunset55802664497482299853514Sunset558026644974822998535145209-01-20Hien Pham45YanakiCanada1069.09Yanaki458242110655393777763647Yanaki4582421106553937777636475309-01-20Tynia Malone10SunsetCanada239.5Grand Total43520741744325913532328255513748Grand Total435207417443259135323282555137485409-01-20Kiki Sho7QuadWest305.835509-01-20Janis Figueroa6Crested BeautMidWest161.7=APIVOT(Rev,4,5,6,)Sum of Net RevenueColumn Labels5609-01-20Chantel Mims1YanakiWest26.95(4\5) 6 vf=0CanadaEastMexicoMidWestNorthWestSouthWestGrand TotalRow LabelsCanadaEastMexicoMidWestNorthWestSouthWestGrand Total5709-01-20Tynia Malone475YanakiNorthWest7633.84Crested Beaut2724.753153.696338.655452.0306184.11838.5625691.78Crested Beaut2724.753153.696338.655452.036184.11838.5625691.785809-01-20Chantel Mims33Crested BeautMexico699.85Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.12Majestic Beaut524.251161.19339.56925.851598.786614.86630.7523795.125909-01-20Janis Figueroa75QuadCanada2457.56Quad5794.927543.4517379.5727417.666908.4833327.4711781.9110153.47Quad5794.927543.4517379.627417.666908.4833327.511781.9110153.476009-01-20Chantel Mims50Majestic BeautNorthWest1348.13Sunset1155.5912777.589997.947512.927533.96639.6714181.453799.05Sunset1155.5912777.69997.947512.927533.96639.6714181.453799.056110-01-20Janis Figueroa7SunsetNorthWest174.65Yanaki1069.0914095.56579.9517433.739222.856298.2412482.261181.59Yanaki1069.0914095.6579.9517433.739222.856298.2412482.261181.596210-01-20Chantel Mims575QuadSouth14114.38Grand Total11268.638731.4734635.6164742.1925264.0753064.2846914.8274621.01Grand Total11268.638731.534635.664742.1925264.0753064.346914.8274621.016310-01-20Janis Figueroa150YanakiEast3039.756411-01-20Kiki Sho300Majestic BeautMidWest5856.386511-01-20Janis Figueroa400SunsetMidWest5508.5dynamic APIVOT interface usingASELECT(proves that APIVOT works with dynamic arrays,no need of tables, no refresh)6611-01-20Chantel Mims32YanakiEast760.24date 1date 2row startrow end6711-01-20Chantel Mims28Crested BeautMexico617.6103-01-2005-01-2012276811-01-20Kiki Sho41Crested BeautWest904.36=APIVOT(H70#,1,2,3,)6911-01-20Chantel Mims425SunsetWest5852.78=ASELECT(Rev,J67,K67,,,{2,4,6})(1\2) 3 vf=0Crested BeautQuadSunsetYanakiGrand Total7012-01-20Chantel Mims6Majestic BeautNorthWest215.7Hien PhamCrested Beaut1816.5Chantel Mims2896.37350.656714.564608.2421569.757112-01-20Hien Pham1Majestic BeautWest33.95Chantel MimsYanaki188.65Hien Pham1816.50916.09195.652928.247212-01-20Chantel Mims40SunsetEast814.3Hien PhamYanaki139.75Janis Figueroa013188.920013188.927312-01-20Hien Pham28YanakiNorthWest689.01Chantel MimsQuad1374.05Kiki Sho778.86907.16001686.027412-01-20Chantel Mims34Crested BeautEast721.06Janis FigueroaQuad13188.92Tynia Malone0074.85074.857512-01-20Hien Pham550SunsetMexico7574.19Chantel MimsSunset6714.56Grand Total5491.6621446.737705.54803.8939447.787613-01-20Chantel Mims7QuadMexico312.83Hien PhamSunset916.097713-01-20Hien Pham9YanakiMexico251.55Tynia MaloneSunset74.857813-01-20Kiki Sho10Crested BeautEast249.5Chantel MimsCrested Beaut934.2top left corner cell format7913-01-20Chantel Mims250Majestic BeautSouth6291.25Chantel MimsCrested Beaut1746.5(rows\columns) values value field=8013-01-20Hien Pham9Majestic BeautSouth323.55Hien PhamYanaki55.9kept it in this shape because if we pivot regular arrays, they do not have headers8113-01-20Tynia Malone375YanakiSouth6242.34Kiki ShoQuad907.168214-01-20Tynia Malone425QuadSouth10921.12Chantel MimsCrested Beaut215.68314-01-20Hien Pham275QuadNorthWest6908.48Kiki ShoCrested Beaut778.868414-01-20Kiki Sho44QuadMexico1596.61Chantel MimsYanaki4419.598514-01-20Janis Figueroa1Majestic BeautNorthWest34.95Chantel MimsQuad5976.68614-01-20Kiki Sho125Crested BeautEast2183.13APIVOT postCell FormulasRangeFormulaH1,H69,L68,H55,H46,H37,H28,H19,H10H1=FORMULATEXT(H2)H2:N8H2=APIVOT(Rev,2,4,6,)H11:N17H11=APIVOT(Rev,2,4,6,1)H20:N26H20=APIVOT(Rev,2,4,6,2)H29:N35H29=APIVOT(Rev,2,4,6,3)H38:N44H38=APIVOT(Rev,2,4,3,)H47:P53H47=APIVOT(Rev,4,5,3,)H56:P62H56=APIVOT(Rev,4,5,6,)J67J67=XMATCH(H67,Rev[Date],1)K67K67=XMATCH(I67,Rev[Date],-1)L69:Q75L69=APIVOT(H70#,1,2,3,)H70:J85H70=ASELECT(Rev,J67,K67,,,{2,4,6})Dynamic array formulas.


----------



## Xlambda (May 29, 2022)

I tried before to post *max Order amount/Salesperson for each month and year*, but because the result was too wide for the mini-sheet (large names) I didn't.
I came up with the idea to split the names and to *display only the surnames* with ATEXTSPLIT and works on both directions, as rows and column values. TEXTSPLIT does not work because it cannot split arrays.
For amounts, to make them shorter, used INT.
LHPIVOT.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1sample table 600 rows2SalespersonOrder IDOrder dateOrder amountOrdered by Salesperson's surnames3Jack Potter328404-01-214686.244Max Order amount/Salesperson for each month and year (integers)4Bob Caldwell328504-01-2117702.13=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),S[Order amount],LAMBDA(x,INT(MAX(x))))5Bob Caldwell328605-01-218563.469PTCaldwellChowdhryCodeGrosseHintonHofferHollinworthJohnsonLambleOnslowPotterShandSmithTreflWoodcockGT6Taj Shand328706-01-2120481.3321-(01)-Jan1787380912325021544192491542023132188541578023344212122128518023233447Jack Potter328809-01-2115780.9921-(02)-Feb215961944414907229692430423031138801621116216207491710623126173321881424965249658Justine Hoffer328911-01-217270.79521-(03)-Mar22231963219659205321225814983112891886523519242191209919766219832384224517245179Sheridan Smith329011-01-215840.16921-(04)-Apr4589245851398422632203592385559002304123927184351257118338129832458510Taj Shand329111-01-2115376.8121-(05)-May12062190452397014546153935654225189768474511399183491023919514188482397011Archer Lamble329212-01-2123132.9821-(06)-Jun79251585252361650718785207108107225141184617810175532459422697171572459412Hudson Hollinworth329312-01-2119249.7821-(07)-Jul1500823903219862394217984247201117522152223327810157451945924162146562472013Hudson Onslow329412-01-2118854.7621-(08)-Aug5385121072400423602850023512221401088082212410020566158119017165402410014Justine Hoffer329512-01-2121544.8121-(09)-Sep2228023227232612110317902234552280320284230908809196012268110304229822345515Taj Shand329613-01-2123344.8721-(10)-Oct574613706201471936823489190552369711890102392067423481216611380424939223902493916Don Johnson329715-01-2115420.9321-(11)-Nov1599824245137558443196802417920375218842301422485242822471421808149962471417Amy Trefl329816-01-2115300.2621-(12)-Dec227852345520777180992386823874121952238622008234954279232772319817051205152387418Lily Code329918-01-218091.06922-(01)-Jan1566021813225172283617982241972391417463196022213923864239162358124782224392478219Hudson Onslow330018-01-2116146.7122-(02)-Feb1612814644249992177716537223912344517285166351282391198400240972499920Bob Caldwell330118-01-215858.817GT2278524585240042499924304247202391423445235192421923927242822471424939249652499921Kayla Woodcock330219-01-2118023.4222Sheridan Smith330320-01-2121212.96Ordered by surnames23Hudson Hollinworth330421-01-216270.728Top 2 Orders amount/Sales person all time (integers)24Archer Lamble330521-01-2111684.54=APIVOT(INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),,S[Order amount],LAMBDA(x,INT(LARGE(x,SEQUENCE(2)))))25Sophia Hinton330621-01-2123250.87PTGT26Hudson Onslow330721-01-215584.412Caldwell22785, 2228027Hudson Hollinworth330821-01-2118973.51Chowdhry24585, 2424528Kayla Woodcock330923-01-2115544.88Code24004, 2397029Sophia Hinton331025-01-219941.139Grosse24999, 2394230Lily Code331125-01-215418.415Hinton24304, 2386831Hudson Onslow331226-01-2116683.98Hoffer24720, 2419732Amy Trefl331328-01-2121285.61Hollinworth23914, 2385533Bob Caldwell331428-01-2117873.08Johnson23445, 2280334Amy Trefl331529-01-2118233.58Lamble23519, 2313235Amy Trefl331629-01-2120485.99Onslow24219, 2410036Archer Lamble331729-01-2110979.77Potter23927, 2386437Hudson Hollinworth331829-01-2116994.46Shand24282, 2415138Justine Hoffer331930-01-219521.601Smith24714, 2459439Bob Caldwell332030-01-219560.26Trefl24939, 2478240Sheridan Smith332130-01-2114020.61Woodcock24965, 2451741Bob Caldwell332230-01-2113538.7GT24999, 2496542Bob Caldwell332301-02-2121596.0743Sheridan Smith332401-02-218228.796PIV 11Cell FormulasRangeFormulaH4,F24H4=FORMULATEXT(H5)H5:X20H5=APIVOT(TEXT(S[Order date],"yy-(mm)-mmm"),INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),S[Order amount],LAMBDA(x,INT(MAX(x))))F25:G41F25=APIVOT(INDEX(ATEXTSPLIT(S[Salesperson]," "),,2),,S[Order amount],LAMBDA(x,INT(LARGE(x,SEQUENCE(2)))))Dynamic array formulas.


----------



## Xlambda (May 31, 2022)

Cool challenge, today's ExcelIsFun YT (31May22) : Unique List of Case Sensitive Items? Expandable Range in Spilled Array Formula? EMT 1792
Will see how to solve it with APIVOT, but first , as stand-alone task, 2 solutions.
*1st solution: expandable ranges*
Formula, single cell G21 :

```
=LET(a,P[Product],
         m,MAP(SEQUENCE(ROWS(a)),a,LAMBDA(x,y,SUM(--EXACT(INDEX(a,SEQUENCE(x)),y)))),
         f,FILTER(a,m=1),
         b,BYROW(f,LAMBDA(x,SUM(--(EXACT(x,a))))),
         VSTACK({"Product","Count"},HSTACK(f,b))
)
```
EMT1792.xlsxABCDEFGHIJKLMNO12ProductSalesSol 1. Expandable range3Carlota44CARLOTA9step 15Carlota7=MAP(SEQUENCE(ROWS(P)),P[Product],LAMBDA(x,y,SUM(--EXACT(INDEX(P[Product],SEQUENCE(x)),y))))6Sunset7↓↓↓7SUNSET61step 28Carlota41=FILTER(P[Product],E7#=1)9CARLOTA42↓↓↓10Aspen41↓↓↓step 311Aspen61↓↓↓=BYROW(G12#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))12ASPEN13Carlota313CARLOTA112CARLOTA3141Sunset1152SUNSET1161Aspen2173ASPEN11819step 4, single cell2021ProductCount22Carlota323CARLOTA324Sunset125SUNSET126Aspen227ASPEN128291792Cell FormulasRangeFormulaE5E5=FORMULATEXT(E7)E7:E17E7=MAP(SEQUENCE(ROWS(P)),P[Product],LAMBDA(x,y,SUM(--EXACT(INDEX(P[Product],SEQUENCE(x)),y))))G8G8=FORMULATEXT(G12)I11I11=FORMULATEXT(I12)G12:G17G12=FILTER(P[Product],E7#=1)I12:I17I12=BYROW(G12#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))G21:H27G21=LET(a,P[Product],
m,MAP(SEQUENCE(ROWS(a)),a,LAMBDA(x,y,SUM(--EXACT(INDEX(a,SEQUENCE(x)),y)))),
f,FILTER(a,m=1),b,BYROW(f,LAMBDA(x,SUM(--(EXACT(x,a))))),
VSTACK({"Product","Count"},HSTACK(f,b)))Dynamic array formulas.


----------



## Xlambda (May 31, 2022)

*2nd solution, non-expandable range, embedded lambda to replace 2 BYROWs.*
Formula, single cell B29:

```
=LET(p,P[Product],
         t,TOROW(p),a,EXACT(p,t),u,--UNIQUE(a),
         b,LAMBDA([i],BYROW(u,LAMBDA(x,LET(y,XLOOKUP(1,x,t),z,SUM(x),IF(i,z,y))))),
         VSTACK({"Product","Count"},HSTACK(b(),b(1)))
)
```
EMT1792.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAAB1Sol 2. non expandable range2ProductSalesstep 13Carlota4=--EXACT(P[Product],TOROW(P[Product]))4CARLOTA9101001000005Carlota7010000100016Sunset7101001000007SUNSET6000100000008Carlota4000010000009CARLOTA41010010000010Aspen40100001000111Aspen60000000110012ASPEN10000000110013CARLOTA11000000000101401000010001step 315=BYROW(E18#,LAMBDA(x,XLOOKUP(1,x,TOROW(P[Product]))))16step 2↓↓↓step 417=UNIQUE(E4#)↓↓↓=BYROW(Q18#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))1810100100000Carlota31901000010001CARLOTA32000010000000Sunset12100001000000SUNSET12200000001100Aspen22300000000010ASPEN12425instead of 2 BYROWS we can use only 1, b([ i ])26 =b()27single cell=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))()28↓↓↓ =b(1)29ProductCount↓↓↓=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))(1)30Carlota3Carlota331CARLOTA3CARLOTA332Sunset1Sunset133SUNSET1SUNSET134Aspen2Aspen235ASPEN1ASPEN1361792Cell FormulasRangeFormulaE3,S29,S17,E17E3=FORMULATEXT(E4)E4:O14E4=--EXACT(P[Product],TOROW(P[Product]))Q15,Q27Q15=FORMULATEXT(Q18)E18:O23E18=UNIQUE(E4#)Q18:Q23Q18=BYROW(E18#,LAMBDA(x,XLOOKUP(1,x,TOROW(P[Product]))))S18:S23S18=BYROW(Q18#,LAMBDA(x,SUM(--EXACT(x,P[Product]))))B29:C35B29=LET(p,P[Product],
         t,TOROW(p),a,EXACT(p,t),u,--UNIQUE(a),
         b,LAMBDA([i],BYROW(u,LAMBDA(x,LET(y,XLOOKUP(1,x,t),z,SUM(x),IF(i,z,y))))),
         VSTACK({"Product","Count"},HSTACK(b(),b(1)))
)Q30:Q35Q30=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))()S30:S35S30=LAMBDA([i],BYROW(E18#,LAMBDA(x,LET(y,XLOOKUP(1,x,TOROW(P[Product])),z,SUM(x),IF(i,z,y)))))(1)Dynamic array formulas.


----------



## Xlambda (Jun 1, 2022)

*Solution with APIVOT*
Again, the advantage that *APIVOT can handle* as arguments *array calculations*.
We could have changed PIVOT to handle these types of scenarios, but because it is not too common in real life, whenever we need it, we can adapt a formula to every specific case.
LHPIVOT.xlsxABCDEFGHIJKLMNOPQ1appending a "_" at the end of the UPPER ones2ProductSales=MAP(P[Product],LAMBDA(x,IF(EXACT(XLOOKUP(x,E4#,E4#),x),x,x&"_")))3Carlota4=UNIQUE(P[Product])4CARLOTA9CarlotaCarlota=APIVOT(G4#,,P[Sales],LAMBDA(x,COUNT(x)),"Count")5Carlota7SunsetCARLOTA_CountGT6Sunset7AspenCarlotaAspen2=APIVOT(G4#,,P[Sales],LAMBDA(x,SUM(x)),"Sum")7SUNSET6SunsetASPEN_1SumGT8Carlota4SUNSET_Carlota3Aspen109CARLOTA4CarlotaCARLOTA_3ASPEN_110Aspen4CARLOTA_Sunset1Carlota1511Aspen6AspenSUNSET_1CARLOTA_2412ASPEN1AspenGT11Sunset713CARLOTA11ASPEN_SUNSET_614CARLOTA_GT631516real life17=APIVOT(P[Product],,P[Sales],LAMBDA(x,COUNT(x)),"Count")18CountGT19Aspen3=APIVOT(P[Product],,P[Sales],LAMBDA(x,SUM(x)),"Sum")20Carlota6SumGT21Sunset2Aspen1122GT11Carlota3923Sunset1324GT632526=APIVOT(P[Product],,P[Sales],LAMBDA(x,AVERAGE(x)),"Avrg")27AvrgGT28Aspen3.66666729Carlota6.530Sunset6.531GT5.72727332PIV 12Cell FormulasRangeFormulaG2G2=FORMULATEXT(G4)E3,I26,L19,I17,L6,I4E3=FORMULATEXT(E4)E4:E6E4=UNIQUE(P[Product])G4:G14G4=MAP(P[Product],LAMBDA(x,IF(EXACT(XLOOKUP(x,E4#,E4#),x),x,x&"_")))I5:J12I5=APIVOT(G4#,,P[Sales],LAMBDA(x,COUNT(x)),"Count")L7:M14L7=APIVOT(G4#,,P[Sales],LAMBDA(x,SUM(x)),"Sum")I18:J22I18=APIVOT(P[Product],,P[Sales],LAMBDA(x,COUNT(x)),"Count")L20:M24L20=APIVOT(P[Product],,P[Sales],LAMBDA(x,SUM(x)),"Sum")I27:J31I27=APIVOT(P[Product],,P[Sales],LAMBDA(x,AVERAGE(x)),"Avrg")Dynamic array formulas.


----------

