using 1 through 9...equal 23
 

Trying to figure out how many combinations there are using the numbers 1 through 9 4 inputs to equal 23 without repeating the combination.

Example:

9+9+4+1=23
9+9+3+2=23
and so on...there has to be a simple way to figure this out besides writing it out.

Thanks for any help

using 1 through 9...equal 23
 

Rich,

I'm not sure from your post whether the order of numbers is important in the equation (i.e. is 9+9+4+1 the same as 1+4+9+9?). The following macro will list each combination of 4 numbers adding to 23 in columns A:D of the active sheet. It will prompt you for whether order of numbers is important. It's probably not a very efficient macro, but it works for me.

Incidentally, the macro calculates 420 combinations when order is important and 28 when it is not.

Hope this helps.

Ben

Sub CountTo23()
Dim a As Long, b As Long
Dim c As Long, d As Long
Dim Counter As Long
Dim i As Long

Counter = 1
Application.ScreenUpdating = False

For a = 1 To 9
For b = 1 To 9
For c = 1 To 9
For d = 1 To 9
If a + b + c + d = 23 Then
Range("A" & Counter).Value = a
Range("B" & Counter).Value = b
Range("C" & Counter).Value = c
Range("D" & Counter).Value = d
Counter = Counter + 1
End If
Next d
Next c
Next b
Next a

If MsgBox("Is the order of numbers important?" & vbCr & vbCr & _
"(i.e. is 9+9+4+1 distinct from 9+9+1+4?)", vbYesNo + vbQuestion, "Does order matter?") _
= vbYes Then GoTo 100

For i = 1 To Counter - 1
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A" & i & ":D" & i), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A" & i & ":D" & i)
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Next i

100: ActiveSheet.Range("$A$1:$D$" & Counter - 1).RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
Range("A1").Activate
Application.ScreenUpdating = True

MsgBox "There are " & Range("A1").End(xlDown).Row & " combinations adding up to 23."

End Sub