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

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

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

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

### User defined function category

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

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

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

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

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

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

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

## Excel formula categories

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