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

Create a Solver model...

...by 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."

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

Re: Create a Solver model...

Mark,
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.
Richard

Re: Create a Solver model...

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

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".