Article updated on February 24, 2018

Here is a useful feature I recently found googling for Excel solver examples.

I have summed some random values from the list A1:A11 in cell C1.

How do I find those summed numbers in C1?

I am going to use Excel Solver. Using more than 20 values (Column A) is not recommended unless you have a lot of spare time.

Watch a video

Install Solver add-in

  1. Click Office button
  2. Click Excel Options
  3. Click add-ins
  4. Install Solver
  5. Click OK

Interested in what other things Excel Solver can do? See category for Solver.

Setup formula

  1. Double click B12
  2. Type =SUMPRODUCT(B1:B11,A1:A11) + ENTER

Recommended reading:

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Start Excel 2007 Solver

  1. Click "Data" in the Ribbon
  2. Click "Solver"
  3. Click "Set Target Cell:" button
  4. Click B12
  5. Type in "Value of:" window the summed value 2615,4
  6. Click "By Changing Cells:" button and select B1:B11.
  7. Click "Add" button
  8. Click "Cell Reference:" button and select B1:B11
  9. Select "bin" in drop down menu (triangle)
  10. Click "OK"
  11. Click "Options"
  12. Enable "Assume Linear Model"
    If you have a newer Excel version and can't find "Assume Linear Model", select Simplex LP in "Select s Solving Method" drop down list.
    Then click "Solve"button
    You are ready, ignore steps below.
  13. I changed "Max Time:" to 1000 seconds
  14. Click OK
  15. Click "Solve"A couple of minutes later.
  16. Click OK!

Recommended articles

Find numbers in sum [UDF]

This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]

Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Find positive and negative amounts that net to zero [UDF]

This article describes a UDF that finds positive and negative numbers that net to approximately zero. You specify the range […]