Search code examples
excelexcel-formuladynamic-arraysexcel-365

Remove double values and sum the values

I'm trying to remove the double values of the first column and make the sum of the values in the others column for this double.

I'm using dynamics arrays of Excel 365 (In French), I will try to translate correctly the formula.

So I have this data sheet:

enter image description here

I make it dynamic array using Filter for example in te cell G5:

=FILTER(B5:E10;B5:B10<>"")

And the goal is to obtain dynamicly this table:

enter image description here

I can filter the first column like this:

=UNIQUE(CHOOSECOL(G5#;1))

But now, how to summarize dynamically the other columns?

When I'm using this formula, it doesn't work :

=SUM.IF(CHOOSECOL(G5#;1);G12#;CHOOSECOL(G5#;2))

But this one work:

=SUM.IF(G5:G10;G12#;H5:H10)

How to get it?

Solution

  • You could try using MMULT( )

    enter image description here

    • Formula used in cell F1

    =LET(
     a,A1:D7,
     b,TAKE(a,1),
     c,DROP(a,1),
     d,TAKE(c,,1),
     e,UNIQUE(d),
     f,DROP(c,,1),
     VSTACK(b,HSTACK(e,MMULT(N(TOROW(d)=e),f))))

    Or, you could Code-Golf it

    enter image description here

    • Formula used in cell F2

    =LET(
     a,N(A2:A7=TOROW(A2:A7)),
     VSTACK(A1:D1,
     UNIQUE(HSTACK(A2:A7,MMULT(a,B2:D7)))))