Formula to aggregate matrix spilling both rows and columns

New Contributor

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={1,2,2}

 

Where both vectors attempt to represent categorical data.

 

What I'd like to get is a new, more aggregated array, where the row and column vectors are grouped by their distinct (unique) elements, and the original array elements added.

 

I could achieve this, for example, by using an old-school array formula (only difference with legacy Excel being I can now enter it without CSE) that I can copy across the output range. See example file.

 

But, I'd like the process to be dynamic so if the original matrix or any of its vectors change, the result will adjust accordingly. I could achieve dynamic row and column totals using the MMULT / SEQUENCE combo, but this is turning out to be very challenging.

Any ideas would be appreciated!

 

Thanks in advance

Diegol

1 Reply

BTW for those interested in getting dynamic row and column totals, see the links below:

https://exceljet.net/formula/get-row-totals

https://exceljet.net/formula/get-column-totals