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