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

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

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

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

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

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]

The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]

In this vba tutorial I am going to show you how to return values from an udf, depending on where […]

### 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.