MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Getting Excel to Display Cells Which can add up to a given number.

Posted by Todd Acker on November 08, 2001 12:48 PM

Is there a way in excel or any other program to search a range of cells and show possible combinations of those cells which can add up to a given number. A simple example being if say Cells A:1, A:2 and A:3 are 1,2,and 3 respectively. I am looking to see if any combination of those cells = 5. This is a very simplified version, i actually have a long spreadsheet and I am trying to add up numbers to arrive at a given total and it takes forever. If anybody knows anything please let me know.

Posted by Mark W. on November 08, 2001 1:16 PM

Create a Solver model... entering the formula, =SUMPRODUCT(A1:A3,B1:B3),
into cell B4, and choosing the Tools | Solver...
menu command. The target cell is B4 which
should be "Equal To" the "Value of" 5. The
"By Changing Cells" range should be B1:B3. You
need to [ Add ] a binary ("bin") constraint on
B1:B3. When you press [ Solve ] it will flag
the values totalling 5 with a 1. If there isn't
a set of values in A1:A3 that total 5 Solver
will respond with the message, "Solver could
not find a feasible solution."

Posted by Jeff Larson on November 08, 2001 1:18 PM

This should work for a 4x4 array of cells to be tested. You can obviously change the size of the matix and the check value. Also, I simply reported the pairs of cells that added up to CheckValue in an empty column. You'll probably want to change the reporting as well.

Sub AddUp()

Dim Matrix(4, 4) As Variant
Dim CheckValue As Variant
Dim Lastrow As Integer
Lastrow = 1
CheckValue = 8

For i = 1 To 4
For j = 1 To 4
Matrix(i, j) = Cells(i, j).Value
Next j
Next i

For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4

If Cells(i, j).Value + Cells(k, l).Value = CheckValue Then
Cells(Lastrow, 5).Value = i & "," & j & "," & k & "," & l
Lastrow = Lastrow + 1
End If
Next l
Next k
Next j
Next i

End Sub

Posted by Richard S on November 08, 2001 5:54 PM

Re: Create a Solver model...

I had a fiddle with this using A1:A5 with numbers 1 through 5, and a target solution of 7. It flagged 2 & 5, but not 3 & 4 or 1, 2 & 4. I think Todd may be looking for multiple solutions, ie there may be a number of combinations of numbers that meet the criteria. Can solver do this? He will also need the Solver Add-in installed. Nice function though.

Posted by todd on November 09, 2001 5:43 AM

Re: Create a Solver model...

i tried this on my spreadsheet but it told me there were too many adjustable is a pretty big sheet

Posted by Mark W. on November 09, 2001 6:23 AM

Re: Create a Solver model...

My recommendation was based on Todd's
statement, "I am looking to see if any
combination of those cells = 5". I
responded to his use of "any" rather
than "all".