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?
CodePudding user response：
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:
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 
- Create a complete list of all possible permutations;
- Filter step 2 based on a sum per row and unique numbers (don't use values from col2 more than once);
- Lookup each value per row to create a total sum per row;
- Return only the unique summations as per screenshot at the top.