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)