Author: Oscar Cronquist Article last 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. Press with left mouse button on Office button
  2. Press with left mouse button on Excel Options
  3. Press with left mouse button on add-ins
  4. Install Solver
  5. Press with left mouse button on OK

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

Setup formula

  1. Double press with left mouse button on 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.

How to use the SUMPRODUCT function

Start Excel 2007 Solver

  1. Press with left mouse button on "Data" in the Ribbon
  2. Press with left mouse button on "Solver"
  3. Press with left mouse button on "Set Target Cell:" button
  4. Press with left mouse button on B12
  5. Type in "Value of:" window the summed value 2615,4
  6. Press with left mouse button on "By Changing Cells:" button and select B1:B11.
  7. Press with left mouse button on "Add" button
  8. Press with left mouse button on "Cell Reference:" button and select B1:B11
  9. Select "bin" in drop down menu (triangle)
  10. Press with left mouse button on "OK"
  11. Press with left mouse button on "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 press with left mouse button on "Solve"button
    You are ready, ignore steps below.
  13. I changed "Max Time:" to 1000 seconds
  14. Press with left mouse button on OK
  15. Press with left mouse button on "Solve"A couple of minutes later.
  16. Press with left mouse button on 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 in sum [UDF]

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 numbers closest to sum

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

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

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