## Find numbers in sum [UDF]

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:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

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 add the User definedÂ Function to your workbook

- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy code above
- Paste code to code module
- Exit visual basic editor and return to excel

### Find numbers in sum category

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

### User defined function category

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates a user definedÂ function that lists files in a ggiven folder and subfolders. A user defined function is […]

## Excel categories

### 4 Responses to “Find numbers in sum [UDF]”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[…] 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 […]

I am receiving an "Syntax" Error related to the Do Until line

ALISTAIR JONES,

This line

Do Until com(k) <= c - p com(k - 1) = com(k - 1) + 1 k = k - 1 p = p + 1 Loop Do Until k >= i

should look like this

Do Until com(k) <= c - p

com(k - 1) = com(k - 1) + 1

k = k - 1

p = p + 1

Loop

Do Until k >= i

Thanks for telling me, I have changed the article.