1

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?

Mayukh Bhattacharya
29.3k9 gold badges31 silver badges46 bronze badges
asked Jul 18, 2023 at 16:04

1 Answer 1

1

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)))))
answered Jul 18, 2023 at 17:28
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, you save me. So, for french people : VSTACK = ASSEMB.V / HSTACK = ASSEMB.H / TAKE = PRENDRE / DROP = EXCLURE / MMULT = PRODUITMAT / TOROW = DANSLIGNE and replace all ',' by ';'. :-)
in just one line, i have learn so much. Very nice usage of LET and MMULT

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.