Identifying cells that add up to a certain amount

[Hifi]

I have a series of numbers (1,2,4,5,6,7,9,11,13,14,15,16). Is there a
formula or way that gives me the result of any two numbers (or any 3 numbers
or 4, etc.) that totals to 20. For example, 9 + 11=20; 2 + 5 + 13 =20; 1 +
2+ 4 + 13= 20; as well as 7+13=20 so on.

Will scenarios in Excel 2007 do this? I poked around with SUMIF but the
difficulty is that you have different cells that can add up to 20 and I don't
know how one would allow for selecting different cells. I looked briefly at
conditional formatting but again you run into the problem of the varied cells
that can add up to 20. I also looked at arrays but did not see anything
there that allows different elements in the array to be added in such a way
as to equal 20 and then identifying which elements were used. I also
investigated lookup with arrays and that, too, does not address my need -- at
least, based on how I have used them in the past.

 

[Jarek Kujawa]

you can use this macro (works for 2 numbers) to accomplish this -
place yr number in different cells in 1 column

Sub sumup_to_20()

For Each cell In Selection
For i = 1 To Selection.Cells.Count - 1
If cell + cell.Offset(i, 0) = 20 Then
cell.Offset(0, 1) = cell
cell.Offset(0, 2) = cell.Offset(i, 0)
End If
Next i
Next cell

End Sub

based on this macro you can write yr own function too

 

[Jarek Kujawa]

here comes for 3 numbers:

sub sumup_3numbs_to_20

For Each cell In Selection
For i = 1 To Selection.Cells.Count
For j = 1 To Selection.Cells.Count
suma = cell + cell.Offset(i, 1) + cell.Offset(j, 2)
If Len(cell) > 0 And Len(cell.Offset(i, 1)) > 0 And
Len(cell.Offset(j, 2)) > 0 And suma = 20 Then
cell.Offset(0, 4) = cell
cell.Offset(0, 5) = cell.Offset(i, 1)
cell.Offset(0, 6) = cell.Offset(j, 2)
End If
Next j
Next i
Next cell

End Sub

macro for 4 numbers would require 4 loops, etc.

HIH

 

[Jarek Kujawa]

for 2 and 3 numbers you would need to delete the "Len(cell) > 0 And
Len(cell.Offset(i, 1)) > 0 And Len(cell.Offset(j, 2)) > 0" conditions
from IF statement

 

[Jarek Kujawa]

here comes for 4:


Sub sumup_to_20()


For Each cell In Selection
For i = 1 To Selection.Cells.Count
For j = 1 To Selection.Cells.Count
For k = 1 To Selection.Cells.Count
suma = cell + cell.Offset(i, 1) + cell.Offset(j, 2) +
cell.Offset(k, 3)
If Len(cell) > 0 And Len(cell.Offset(i, 1)) > 0
And Len(cell.Offset(j, 2)) > 0 And Len(cell.Offset(k, 3)) > 0 And suma
= 20 _
And cell <> cell.Offset(i, 1) And cell.Offset(i,
1) <> cell.Offset(j, 2) And cell.Offset(j, 2) <> cell.Offset(k, 3) _
And cell <> cell.Offset(j, 2) And cell <>
cell.Offset(k, 3) _
And cell.Offset(i, 1) <> cell.Offset(k, 3) Then

licznik = licznik + 1

cell.Offset(licznik - 1, 5) = cell
cell.Offset(licznik - 1, 6) = cell.Offset(i,
1)
cell.Offset(licznik - 1, 7) = cell.Offset(j,
2)
cell.Offset(licznik - 1, 8) = cell.Offset(k,
3)
End If



Next k
Next j
Next i
Next cell



End Sub

this time I assumed that no number can be repeated in a series

 

[Hifi]

Thanks so much....that does the trick!

Much appreciated -- I had not thought to go to a macro.