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

XL123

New Member
Joined
Aug 4, 2014
Messages
11
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?
5451Answer:
65181,6
76802,9
87494,7
98137,10
10935
111051
12
Sheet1
 

Attachments

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

XL123

New Member
Joined
Aug 4, 2014
Messages
11
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
Joined
Jan 14, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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?
5451Answer:
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
Joined
Aug 4, 2014
Messages
11
Thanks. This is a very interesting solution. Would have never thought of. You are gifted my friend.
 

tboulden

New Member
Joined
Jan 14, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 4, 2014
Messages
11
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
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top