## Excel udf: Find numbers in sum

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:

*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**

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

### Download excel sample file for this tutorial.

Sum combinations.xls

(Excel 97-2003 Workbook *.xls)

### 2 Responses to “Excel udf: Find numbers in sum”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] udf that I think could be converted into a macro to do what I want to do. The udf can be found at: Excel udf: Find numbers in sum | Get Digital Help - Microsoft Excel resource The code for the udf: Function Find_num(rng As Range, cell As Range) Dim com() As Single, c As […]

[…] target match, summatch.com, and they won't work with values like: 0.3157907543. I found a udf here: Excel udf: Find numbers in sum | Get Digital Help - Microsoft Excel resource but I need to convert it to a macro to work with the values I have like: 0.3157907543. There are 42 […]