ASUMIFS

=ASUMIFS(f,w,v)

f
"find" or criteria array
w
"within" array
v
values array, to be summed, can have multiple columns array since the function is recursive

sumifs alternative, that can handle array calculations as its arguments, reports friendly

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
109
Office Version
  1. 365
Platform
  1. Windows
ASUMIFS !! recursive !! array sumifs alternative, that can handle array calculations as its arguments . calls T_IFS , APPEND2H
Other functions on minisheet: ADATE , ACOMBINE , ASELECT
Excel Formula:
=LAMBDA(f,w,v,
    LET(r,ROWS(w),n,COLUMNS(v),x,MMULT(T_IFS(f,w,),INDEX(v,,n)),
       IF(n=1,APPEND2H(x,v,2),APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),n-1)),x,))
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1example that justifies the use of ASUMIFS (SUMIFS can not be used bcs of range argument), (quarterly amd monthly report)quarterly report - single cell formula that uses only initial array, no helper arrays needed
2step 1 (transform date clm)step 2 (grouping criteria)step 3(report)please imagine equivalent formula using SUMIFS 😉 (without helper arrays, no way)
3sample "within array"=ADATE(A4:E33,"q",)=ACOMBINE(G4#,{1,2,3})=ASUMIFS(M4#,G4:I33,J4:K33)=ASUMIFS(ACOMBINE(ADATE(A4:E33,"q",),{1,2,3}),ASELECT(ADATE(A4:E33,"q",),,,,3,),J4:K33)
411-02-21Ax8322021-Q1Ax8322021-Q1Ax1517315173single cell formula Z6:=ASUMIFS(ACOMBINE(ADATE(A4:E33,"m",),{1,3}),ASELECT(ADATE(A4:E33,"m",),,,,,{1,3}),J4:K33)
505-07-21By3242021-Q3By3242021-Q1Ay0000=ACOMBINE(ADATE(A4:E33,"m",),{1,3})monthly report for x,y - single cell formula
628-12-21Cy2532021-Q4Cy2532021-Q1Bx13126131262021-01-Janx16168
717-01-21Cx9272021-Q1Cx9272021-Q1By00002021-01-Jany524
822-02-21Cx6342021-Q1Cx6342021-Q1Cx25151251512021-02-Febx22109
902-10-21By3172021-Q4By3172021-Q1Cy5245242021-02-Feby00
1028-09-21Ay9182021-Q3Ay9182021-Q2Ax00002021-03-Marx15173
1106-09-21Ay6342021-Q3Ay6342021-Q2Ay4474472021-03-Mary00
1225-11-21Cy7512021-Q4Cy7512021-Q2Bx00002021-04-Aprx00
1303-03-21Bx5832021-Q1Bx5832021-Q2By3143142021-04-Apry761
1423-07-21Bx4562021-Q3Bx4562021-Q2Cx8348342021-05-Mayx834
1518-09-21Ay5732021-Q3Ay5732021-Q2Cy7377372021-05-Mayy737
1631-03-21Cx8262021-Q1Cx8262021-Q3Ax10125101252021-07-Julx18164
1727-01-21Ax2432021-Q1Ax2432021-Q3Ay20125201252021-07-July1086
1821-10-21Bx1262021-Q4Bx1262021-Q3Bx16175161752021-08-Augx160
1920-07-21Bx5432021-Q3Bx5432021-Q3By108610862021-08-Augy572
2016-07-21By7622021-Q3By7622021-Q3Cx00002021-09-Sepx776
2125-12-21Cy3872021-Q4Cy3872021-Q3Cy5725722021-09-Sepy20125
2217-05-21Cx8342021-Q2Cx8342021-Q4Ax00002021-10-Octx126
2305-07-21Ax9652021-Q3Ax9652021-Q4Ay00002021-10-Octy317
2429-04-21By3142021-Q2By3142021-Q4Bx1261262021-11-Novx00
2524-01-21Ax5982021-Q1Ax5982021-Q4By3173172021-11-Novy751
2625-08-21Ax1602021-Q3Ax1602021-Q4Cx00002021-12-Decx00
2707-05-21Cy7372021-Q2Cy7372021-Q4Cy12191121912021-12-Decy5140
2812-02-21Bx8432021-Q1Bx843
2917-04-21Ay4472021-Q2Ay447check:=SUMIFS(J4:J33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
3021-01-21Cy5242021-Q1Cy524=SUMIFS(K4:K33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
3106-03-21Cx2642021-Q1Cx26415173
3227-09-21Bx7762021-Q3Bx77600
3302-08-21Cy5722021-Q3Cy57213126
3400other functions on minisheet
3525151ASELECT
36sample=ACOMBINE(A37:E51,{1,2,3})=ASUMIFS(ACOMBINE(A37:C51,),A37:C51,D37:E51)524ADATE
37Bcx310Aax12300ACOMBINE
38Aax123Aay21174447
39Bbx245Abx36400
40Acy632Aby00314
41Bby416Acx00834
42Bbx887Acy13157737
43Aay665Bax95510125
44Acy449Bay0020125
45Bby581Bbx1321416175
46Abx364Bby9971086
47Bax955Bcx31000
48Acy376Bcy00572
49Bbx38200
50Aay89000
51Aay719126
52317
53 Note: AGROUP easy to use formula will follow with the capability of 00
54 compacting the whole reports in an array12191
55
ASUMIFS post
Cell Formulas
RangeFormula
G3,R30,W5,M3,Q3,T3G3=FORMULATEXT(G4)
G4:K33G4=ADATE(A4:E33,"q",)
M4:O27M4=ACOMBINE(G4#,{1,2,3})
Q4:R27Q4=ASUMIFS(M4#,G4:I33,J4:K33)
T4:U27T4=ASUMIFS(ACOMBINE(ADATE(A4:E33,"q",),{1,2,3}),ASELECT(ADATE(A4:E33,"q",),,,,3,),J4:K33)
W4W4=FORMULATEXT(Z6)
W6:X27W6=ACOMBINE(ADATE(A4:E33,"m",),{1,3})
Z6:AA27Z6=ASUMIFS(ACOMBINE(ADATE(A4:E33,"m",),{1,3}),ASELECT(ADATE(A4:E33,"m",),,,,,{1,3}),J4:K33)
Q29Q29=FORMULATEXT(Q31)
Q31:Q54Q31=SUMIFS(J4:J33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
R31:R54R31=SUMIFS(K4:K33,G4:G33,M4:M27,H4:H33,N4:N27,I4:I33,O4:O27)
F36,K36F36=FORMULATEXT(G37)
G37:I48G37=ACOMBINE(A37:E51,{1,2,3})
L37:M48L37=ASUMIFS(ACOMBINE(A37:C51,),A37:C51,D37:E51)
Dynamic array formulas.
 
Upvote 0

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Noticed that when I posted first time the formula, I have copied the base case formula for recursive ASUMIFS that works for n<=2, (values array up to 2 columns), and not the general case one.
The only modification, n-1 to SEQUENCE(,n-1) ....APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),n-1)),x,))..to ....APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),SEQUENCE(,n-1))),x,)),,,,
Here is the general one and a complex example that uses array values of 4 columns, and other functions and reports, calls T_IFS , APPEND2H
!! recursive !! ASUMIFS(f,w,v)=LAMBDA(f,w,v,LET(r,ROWS(w),n,COLUMNS(v),x,MMULT(T_IFS(f,w,),INDEX(v,,n)),IF(n=1,APPEND2H(x,v,2),APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),SEQUENCE(,n-1))),x,))))
Other functions on minisheet: ARANDOM , ACOMBINE , ACOUNTIFS , ARUNTOT
Excel Formula:
=LAMBDA(f,w,v,
    LET(r,ROWS(w),n,COLUMNS(v),x,MMULT(T_IFS(f,w,),INDEX(v,,n)),
       IF(n=1,APPEND2H(x,v,2),APPEND2H(ASUMIFS(f,w,INDEX(v,SEQUENCE(r),SEQUENCE(,n-1))),x,))
    )
)
LAMBDA 8.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Random generated arrayRandom generated valuesTotals all values for all possible Runing totals all valuesNr.operationsWhat shop/product/region
2combinations of shop/products/regionby regionfor each shop/product/regionregistered no operation
3shopproductregion=ARANDOM(A4:C7,80)=RANDARRAY(80,4,100,900,1)=ACOMBINE(A4:C7,)=ASUMIFS(M4#,E4#,H4#)=ARUNTOT(P4#,-4)=ACOUNTIFS(M4#,E4#)=FILTER(M4#,NOT(Z4#))
4AXEastDZWest350766184149AXEast3076347441803076347441801AYEast
5BYWestBZWest478722634242AXNorth5985191161775985191161771AZNorth
6CZNorthBXSouth397545449141AXSouth8586558017208586558017201AZWest
7DSouthDYWest448357170569AXWest4451155533724451155533721CYEast
8BZNorth450698720520AYEast00003076347441800CZEast
9CZNorth242264747378AYNorth40855066310451006106977912222CZSouth
10BYSouth273176480485AYSouth168116881859160325392343266023233DXSouth
11DYNorth352383802150AYWest585324383810103043993611821DZSouth
12other functionsBZWest106610305674AZEast18933682018649697015643661
13ARANDOMDZEast122854238381AZNorth00001006106977912220
14ACOMBINEDZWest837408236511AZSouth179825291350300243374872401053254
15ACOUNTIFSAZSouth596498437832AZWest0000103043993611820
16ARUNTOTBYWest392665733819BXEast7051089126177112012059282511372
17BZWest785428103739BXNorth55717756320615631246134214281
18DZNorth630303238783BXSouth653129187827349906163488855982
19BXSouth256746429132BXWest12121282136554622421721230117282
20BZEast130373213506BYEast11641115747116523653174357223022
21AXSouth858655801720BYNorth20958440362517721830174520531
22CXEast877302206721BYSouth15077171827153364976880671571313
23BZSouth250760469216BYWest39266573381926342386303425471
24AYNorth209276437439BZEast144115471227101438064721479933163
25CYSouth600173398666BZNorth124414771440138430163307318534373
26BYSouth801199535473BZSouth279325992566235592909479928194865
27AZSouth315750123654BZWest296329153330360555975301636461526
28AYSouth378224590242CXEast202216181251134458286339605046603
29BYEast810744240291CXNorth7551391834115337714698401945902
30BZEast512836846323CXSouth679869488320996910348976998061
31AYSouth830875515660CXWest13043380613557275734717062871
32DYEast706425268564CYEast000058286339605046600
33AXWest445115553372CYNorth28814266712540594840468647151
34BXNorth557177563206CYSouth600173398666105691052110167104721
35CXEast476884767483CYWest42641648739061536150765766771
36DYNorth480852496172CZEast000058286339605046600
37DXEast869797232308CZNorth5656671599106746245507628557822
38AZSouth287513654808CZSouth0000105691052110167104720
39BYNorth209584403625CZWest56186375466067147013841173371
40BXEast129553852633DXEast1328100075188471567339680155442
41CYWest426416487390DXNorth9181136564150555426643684972872
42BZSouth829216184564DXSouth0000105691052110167104720
43BZWest771329682442DXWest48668267750172007695908878381
44AXNorth598519116177DYEast22171755821165793739094762272013
45CYNorth288142667125DYNorth12821360180882368248003865781103
46CXSouth679869488320DYSouth613256492353111821077710659108251
47BYEast354371507874DYWest113981238989883398507947787362
48CXNorth231855695543DZEast12285423838194959948786075821
49BZSouth891510885228DZNorth63030323878374548306889588931
50DYSouth613256492353DZSouth0000111821077710659108250
51CXEast669432278140DZWest2780289412132715111191140110690114515
52DYEast735703220757
53BZWest432222713683
54DYNorth450125510501
55CXWest130433806135
56BYSouth433342812575left array goes down over this limit
57AYNorth199274226606↓↓↓↓
ASUMIFS gen
Cell Formulas
RangeFormula
E3,H3,Z3,AB3,U3,M3,P3E3=FORMULATEXT(E4)
E4:G83E4=ARANDOM(A4:C7,80)
H4:K83H4=RANDARRAY(80,4,100,900,1)
M4:O51M4=ACOMBINE(A4:C7,)
P4:S51P4=ASUMIFS(M4#,E4#,H4#)
U4:X51U4=ARUNTOT(P4#,-4)
Z4:Z51Z4=ACOUNTIFS(M4#,E4#)
AB4:AD11AB4=FILTER(M4#,NOT(Z4#))
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,890
Messages
5,655,820
Members
418,239
Latest member
RSadler11

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
Top