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 24, 2022

SergeiBaklan

 

I geared the solution a bit more towards the shape I want it to have. I ended up with a single name definition, the lambda itself (actually a lambda called within another lambda), that acts a formula (SpillSum as I named it) with 3 arguments: Data, Labels, Headers.

 

The intention is that the formula can be used within any workbook, any number of times, without the need of defining each of the components as a name time and again. Only requisite is to define SpillSum once in each workbook.

 

As you'll see, it very much sticks to the general algorithm you designed. Thanks again!

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

    diegol81 

    That's good, thanks for sharing

  • 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.

Resources