-3

I have a table:

col1 col2
134 1
432 2
222 3
21 4
982 5
1352 8
111 9

I need to find all possible sum combinations of col1 values IF col2 sum is 10. (5+4+1, 2+3+5, etc.) & number of terms is 3

Please advice how to solve this task?

Andrey Andrey
  • 85
  • 5
  • You best implement a macro for this. Loop through col2 to find all triples that summ up to 10. then add their col1 values. – Felix Jun 09 '22 at 11:01

1 Answers1

1

To get all unique possible sums based on a give count of items in col2 and sum of col2 is a specific amount, with ms365, try:

enter image description here

Formula in D1:

=LET(inp,B1:B7,cnt,3,sm,10,B,COUNTA(inp),A,MAKEARRAY(B,cnt,LAMBDA(r,c,INDEX(inp,r,1))),D,B^cnt,E,UNIQUE(MAKEARRAY(D,cnt,LAMBDA(rw,cl,INDEX(IF(A="","",A),MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1,cl)))),F,FILTER(E,MMULT(--(E<>""),SEQUENCE(cnt,,,0))=cnt),G,FILTER(F,BYROW(F,LAMBDA(a,(SUM(a)=sm)*(COUNT(UNIQUE(a,1))=cnt)))),UNIQUE(BYROW(G,LAMBDA(a,SUM(XLOOKUP(a,inp7,A1:A7))))))

You can now change parameters cnt and sm to whichever amount you like.

The trick is borrowed from my answer here to calculate all permutations first. Instead of a range, the single column input is extended using MAKEARRAY().

A short visual representation of what is happening:

  1. Expand the given array based on cnt;

enter image description here

  1. Create a complete list of all possible permutations;

enter image description here

  1. Filter step 2 based on a sum per row and unique numbers (don't use values from col2 more than once);

enter image description here

  1. Lookup each value per row to create a total sum per row;

enter image description here

  1. Return only the unique summations as per screenshot at the top.
JvdV
  • 70,606
  • 8
  • 39
  • 70