Excel Forum Questions that can be solved with SumMatch® Pro or SumMatch® Web

Have you ever encountered a problem where you need to find a combination of numbers that add up to a given sum?

Have you tried to solve it using Excel? Maybe you have tried to come up with a spreadsheet formula or use Excel Solver add-in to find an answer to this quite popular question. If you have spent some time trying to find your combination of numbers and then realised that there may be more than one good answer to your question – there may be 2, 3 or a lot more ‘good’ combinations of numbers in the set that sum up to your target total. 

You have come to the right place to get all your answers

This is a common problem. It is known as the 0-1 Knapsack problem.
A lot of work has been done by Excel users trying to solve the Knapsack problem. We can point you to some existing Excel solutions here or here. and plenty of other forums with some good advice - see the links at the bottom of this page.

Now that you know what to google for (Knapsack problem, bin packing problem, subset sum problem) you can do even better yourself!

There are some issues with all the solutions you will find on many websites. They do not cope well with large number of inputs. If you have more than 25 numbers and want to identify numbers in your target sum you may wait for a very long time for your answer or you may get only the first solution to the problem. For example your set has 10 different combinations that add up to the given sum but your Excel macro (or Excel Solver add-in) stops after finding the first one.

Some calculations may be too much for a Macro

 

Other macros may look for the solving combinations for a very long time. Let’s see how many sums we have to calculate for 50 different numbers in our input set. The total count of all possible combinations of 50 numbers is 250 = 1,125,899,906,842,620. Let’s face it – even the fastest computer cannot make these calculations within an acceptable time limit.

We have done some work to overcome the limitations of the existing solutions. Now you can solve your problem with or without Excel. Try to use SumMatch Pro add-in for Excel or SumMatch Web in your browser. Both will give you all the possible combinations of numbers adding up to your sum within a relatively short time. It is easy to use. If you still want to use the well-known free Excel add-in Solver we suggest You  watch our youtube video with instructions how to set up the Knapsack problem in Excel using SumMatch and/or Solver.

The following links are provided as a reference: