ExcelBanter - Find numbers in an array that sum to a certain number

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Find numbers in an array that sum to a certain number (https://www.excelbanter.com/excel-worksheet-functions/109011-find-numbers-array-sum-certain-number.html)

Find numbers in an array that sum to a certain number

How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

Find numbers in an array that sum to a certain number

tommydancer wrote...
How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

This is a classic programming problem. The answer is that there's no
guaranteed way to find the solution to this problem that doesn't
require iterating through all possible subsets of the individual
numbers. And if there are N individual numbers, there are 2^N-1
nontrivial subsets. For N-5, that means only checking 31 subsets of the
numbers. For N=20, there's over a million. For N=100, you'd need months
of uninterrupted supercomputer time.

Actually, that overstates the problem a bit, but only a bit. Heuristics
can be applied to limit the subsets, but that only eliminates a few
orders of magnitude.

Use Google Groups advanced search to find previous responses to this
sort of question. You could use the search words SUMPRODUCT and SOLVER
as well as the search phrase "sum to" to find solutions that'd give one
possible combination. If you want all such combinations, you'll need to
use VBA, and it could take a LONG time, long as in hours. Solutions
that'd require days or longer would exceed system resources long before
that.

Find numbers in an array that sum to a certain number

Assuming your data on column A
A1=3
A2=5
A3=7
A4=14
A5=20
On column B
B1=1
B2=1
B3=1
B4=1
B5=1
On column C
A1*B1
A2*B2 so on....

enter formula on cell C6 =SUM(C1:C5)
Tool | Solver | Set Target Cell: $C$6
Equal To: Select Value of: 22
By Changing Cells: $B$1:$B$5 click Add
Change Constraint Cell Reference: $B$1:$B$5 also change <= to int then click
Add
Click Solve, Keep Sover Solution, click OK

"tommydancer" wrote:

How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

Find numbers in an array that sum to a certain number