This article describes how to find a sum from a range of numbers using a user defined function.

Let´s see how quickly excel solver finds the numbers using the instructions from this post: Identify numbers in sum using solver in excel.

The sum is 1832,3 and it takes around 15 seconds to find the numbers.

With my beginner vba skills I created an user defined function to find numbers in a sum. See picture below.

The udf finds two sets of numbers in a second.

Udf in A23:Q25:

=Find_num(A1:A17, B20) + CTRL + SHIFT + ENTER

Formula in R23 sums the numbers..

Here is another example:

User defined function:

Function Find_num(rng As Range, cell As Range)
Dim com() As Single, c As Single, i As Single
Dim s As Single, d As Single, u As Single, v As Single
Dim arr() As String, r As Single, p As Single, t As Single
Dim sum_cells As Single, j As Single, k As Single, l As Single
sum_cells = Application.WorksheetFunction.Sum(rng)
c = rng.Rows.Count
ReDim arr(1 To 1000, 1 To c)
r = 1
For i = 0 To Int(c / 2)
  If i <> Int(c / 2) - 1 Then
    t = WorksheetFunction.Combin(c, i + 1)
  Else
    t = WorksheetFunction.Combin(c, i + 1) / 2
  End If
  ReDim com(i)
  For j = 0 To i
    com(j) = j + 1
  Next
  k = i
  For s = 1 To t
    If com(k) > c And i > 0 Then
      p = 0
      Do Until com(k) <= c - p
        com(k - 1) = com(k - 1) + 1
        k = k - 1
        p = p + 1
      Loop
      Do Until k >= i
        k = k + 1
        com(k) = com(k - 1) + 1
      Loop
    End If
    d = 0
    For j = 0 To i
      d = d + rng(com(j))
    Next j
    If d = cell Then
      For j = 1 To i + 1
        arr(r, j) = rng(com(j - 1))
      Next j
      r = r + 1
    End If
    If sum_cells - d = cell Then
      For j = 1 To c
        v = 0
        For u = 0 To i
          If rng(com(u)) = rng(j) Then v = 1
        Next u
        If v = 0 Then
           arr(r, j) = rng(j)
        End If
      Next j
      r = r + 1
    End If
    com(k) = com(k) + 1
  Next s
Next i
Find_num = arr()
End Function

How to use user defined function in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste the above user defined function
  4. Exit visual basic editor

Download excel sample file for this tutorial.

Sum combinations.xls
(Excel 97-2003 Workbook *.xls)