# Finding all combinations of rows in a column which sum to a given total

#### XL123

##### New Member
Hi all,
I have a sheet shown below which has in Column B a list of numbers - this list can have up to 500 values in the column (I only listed 10 numbers in my simple example). Column A has the row # associated with each value. The sum / total I want to get is in cell E2. Its 100 but this is an input cell the value can be changed to put in any number. Cells F6 to F9 have the answers. The row length can change (e.g. F6 to F15) to show all the possible row combinations which give the sum total of 100.
I tried using a solver method but it only gives 1 possible answer. Am thinking its possible to use dynamic array formulas to do this in combination with the new Lambda function - since it accomodates recursion. Does anyone have a possible solution? I put image of problem as an attachment too....

Get subset adding up to a total from a list of #s.xlsx
ABCDEFGHI
1Row #DataTotal to get
2120100
3265
4372All combination of rows #s adding to to 100?
65181,6
76802,9
87494,7
98137,10
10935
111051
12
Sheet1

#### Attachments

• Excel sum problem.jpg
55.5 KB · Views: 0

#### XL123

##### New Member
Impressive tboulden! This is a great use of dynamic array formulas. How will the formula handle if 3 or 4 combinations of numbers having to be combined to get a required total though?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### tboulden

##### New Member
The essence of this is coming up with pairwise-combinations of 2 lists; in your original its pairwise-combinations of the list of Rows with itself. For 3 or 4, you take the combinations from before and combine again with the list of Rows. Repeat as many times as you like; I've done 3 below, perhaps I'll try to set up a LAMBDA for the more general case later.

Pairwise_Combinations.xlsm
ABCDEFGHIJKLMNOPQRS
1Row #DataTotal to getTotal to getTotal to getTotal to get
2120100172<- Add Row 3118<- Add Row 5113<- Add Row 8
3265
4372All combination of rows #s adding to to 100?
65181,6
76802,9
87494,7
98137,10If you don't want rows double-counted, change <= to < in FILTER
10935
111051Pair SumTriple SumTriple SumTriple Sum
1216136156168
1329239177289
1447347259478
1571037104577810
165710
17
TripleSum
Cell Formulas
RangeFormula
A2:A11A2=SEQUENCE(10)
I2I2=E2+B4
M2M2=E2+B6
Q2Q2=E2+B9
E12:F15E12=LET( rowRng,A2#, dataRng,\$B\$2:\$B\$11, rowPairsCol1,INDEX(rowRng,INT((SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowPairsCol2,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowPairs,CHOOSE({1,2},rowPairsCol1,rowPairsCol2), pairSeq,SEQUENCE(ROWS(rowPairs)), dataSums,INDEX(dataRng,INDEX(rowPairs,pairSeq,),1)+INDEX(dataRng,INDEX(rowPairs,pairSeq,2)), FILTER(rowPairs,(dataSums=E2)*(rowPairsCol1<=rowPairsCol2),"") )
I12:K15I12=LET( rowRng,A2#, dataRng,\$B\$2:\$B\$11, rowPairsCol1,INDEX(rowRng,INT((SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowPairsCol2,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowPairs,CHOOSE({1,2},rowPairsCol1,rowPairsCol2), rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3), tripSeq,SEQUENCE(ROWS(rowTrips)), dataSums,INDEX(dataRng,INDEX(rowTrips,tripSeq,),1)+INDEX(dataRng,INDEX(rowTrips,tripSeq,2))+INDEX(dataRng,INDEX(rowTrips,tripSeq,3)), FILTER(rowTrips,(dataSums=I2)*(rowTripsCol1<=rowTripsCol2)*(rowTripsCol2<=rowTripsCol3),"") )
M12:O16M12=LET( rowRng,A2#, dataRng,\$B\$2:\$B\$11, rowPairsCol1,INDEX(rowRng,INT((SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowPairsCol2,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowPairs,CHOOSE({1,2},rowPairsCol1,rowPairsCol2), rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3), tripSeq,SEQUENCE(ROWS(rowTrips)), dataSums,INDEX(dataRng,INDEX(rowTrips,tripSeq,),1)+INDEX(dataRng,INDEX(rowTrips,tripSeq,2))+INDEX(dataRng,INDEX(rowTrips,tripSeq,3)), FILTER(rowTrips,(dataSums=M2)*(rowTripsCol1<=rowTripsCol2)*(rowTripsCol2<=rowTripsCol3),"") )
Q12:S15Q12=LET( rowRng,A2#, dataRng,\$B\$2:\$B\$11, rowPairsCol1,INDEX(rowRng,INT((SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowPairsCol2,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowPairs,CHOOSE({1,2},rowPairsCol1,rowPairsCol2), rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3), tripSeq,SEQUENCE(ROWS(rowTrips)), dataSums,INDEX(dataRng,INDEX(rowTrips,tripSeq,),1)+INDEX(dataRng,INDEX(rowTrips,tripSeq,2))+INDEX(dataRng,INDEX(rowTrips,tripSeq,3)), FILTER(rowTrips,(dataSums=Q2)*(rowTripsCol1<=rowTripsCol2)*(rowTripsCol2<=rowTripsCol3),"") )
Dynamic array formulas.

#### XL123

##### New Member
Thanks. This is a very interesting solution. Would have never thought of. You are gifted my friend.

#### tboulden

##### New Member
Thanks, glad to help! Struggled with a similar problem a month ago and came up with this approach. One caution: you want the longer list to be first when doing the combinations; if lists are same length, order doesn't matter.

#### XL123

##### New Member
Thanks, glad to help! Struggled with a similar problem a month ago and came up with this approach. One caution: you want the longer list to be first when doing the combinations; if lists are same length, order doesn't matter.
Thank you for the tip. I appreciate your advice

Replies
1
Views
81
Replies
4
Views
71
Replies
1
Views
53
Replies
0
Views
527
Replies
7
Views
72

1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back