Forum Discussion

diegol81's avatar
diegol81
Copper Contributor
Oct 28, 2021
Solved

Formula to aggregate matrix spilling both rows and columns

Hi all,   I have an array originating in one cell and spilling both rows and colums: C5={5,2,1;7,3,4;9,10,8}   I also have row and column names that describe its contents: B5={"A";"A";"B"} C4=...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 21, 2022

    diegol81 , you are welcome.

     

    For data only it's simpler

    uHeader = UNIQUE(header, 1);
uLabels = UNIQUE(labels);

onlyData=
MAKEARRAY( ROWS(ulabels), COLUMNS(uHeader),
    LAMBDA( r, c,
        SUM(
            FILTER(
                FILTER(
                    data,
                    (labels=INDEX(uLabels, r))  ),
                header=INDEX(uHeader, c)
            )
        )
    )
);
diegol81's avatar
diegol81
Copper Contributor
Mar 25, 2022

SergeiBaklan 

 

Thanks for that. Unfortunately the new AFE addin (actually, all the addins accessible via the Insert | Addins | Get addins dialog) download and install seems to have been disabled globally in my company. Hopefully I'll get the admins to enable it.

 

Back to my last post: shortly after positing I realised my solution did not fully resemble the functional structure I was seeking, which is akin to that of a SUMIFS: a data array to add-up; a labels vector to evaluate; a labels condition vector; a (transposed) headers vector to evaluate; a (transposed) headers condition vector. I was missing the two condition arguments, which impaired the formula's functionality.

 

As a result of this review, the LET() call was no longer needed and I renamed the formula SpillSumIfs. Here it goes, hopefully for good.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 26, 2022

    diegol81 

    That's good, thanks for sharing

Resources