Search code examples
excelexcel-formula

what is best way of replicating sumifs but by creating an array formula?

I have the following data set:

enter image description here

and I have the following table:

enter image description here

what I would like to do is create an array formula in the table that would remove the need for many sumifs formulas (such as =SUMIFS(D$10:D$18,$A$10:$A$18,$A3,$B$10:$B$18,"y",$C$10:$C$18,"y")) that would need to be dragged across and down so that we would end up with this:

enter image description here

Solution

  • Try using MAKEARRAY() --> simple and easy to understand:

    enter image description here

    =MAKEARRAY(ROWS(A13:A15),COLUMNS(B12:M12),LAMBDA(r,c,
 SUM((INDEX(A13:A15,r)=A2:A10)*(B2:B10="Y")*(C2:C10="Y")*(INDEX(B12:M12,c)=D1:O1)*D2:O10)))

    Or, Use one single dynamic array formula to return the whole array:

    =LET(
     _Data, DataTable[#All],
     _Name, DROP(TAKE(_Data,,1),1),
     _Headers, DROP(TAKE(_Data,1),,3),
     _Uniq, UNIQUE(_Name),
     _Output, MAKEARRAY(ROWS(_Uniq), COLUMNS(_Headers), LAMBDA(r,c,
     SUM((INDEX(_Uniq,r)=_Name)*(INDEX(_Headers,c)=_Headers)*
     (INDEX(DROP(_Data,1),,2)="Y")*(INDEX(DROP(_Data,1),,3)="Y")*DROP(_Data,1,3)))),
     VSTACK(HSTACK(A1,_Headers),HSTACK(_Uniq,_Output)))

    If applicable, then can use GROUPBY() as well:

    enter image description here

    =VSTACK(HSTACK(A1,D1:O1),
 GROUPBY(A2:A10,D2:O10,SUM,,1,,BYROW(B2:C10="Y",AND)))

    After lot of trials and error found this one to be quicker and efficient than the rest :

    =LET(
     _Conditions, MMULT(N(Conditions="Y"),{1;1})>1,
     _Filter, FILTER(HSTACK(IDData,Vals),_Conditions,""),
     _Name, TAKE(_Filter,,1),
     _Uniq, UNIQUE(_Name),
     _SumVals, MMULT(N(_Uniq=TOROW(_Name)),DROP(_Filter,,1)),
     IFNA(INDEX(_SumVals,XMATCH(ID,_Uniq),B1:M1),0))